Multi field ‘Or’ Query (access 2000)

Topic

New Reply

There is a table in a database which contains one field with people’s names (text field) and 34 other fields each relating to a different course (date field). The date in any course field relates to the date on which each person must retake the course.

A query is required which will list all the people and any course they have not retaken by a particular date. So it could be none, some or all of them (please note I did not design this database and my suggestion of redoing it was met with not a little customer resistance!).

Within a normal query window design view the criteria “or” lines end after 8 options and obviously upwards of 34 are required here!

Could I write more “or’s” in the SQL view? or could is there a bit of code I could include? or is there an easier way?

Thanks

Roberta

Reply | Quote

Replies

You can have a lot more than 8 “or” lines (I’m pretty sure there’s a limit on the complexity of the overall query in Access 2000, but I so far haven’t hit it).

The solution to your question is very simple – Just insert more rows using Insert | Rows in your menu.

Reply | Quote

I just tried it out and it is working.
A query with 36 or’s

The strange thing is that when you write the or in sql view and then switch back to design view, the lines are added to the design view (see the attached mdb, query1).

According the help file, there may be 40 and’s in a query. I suppose that’s the same for OR’s

I really wonder what the speed will be with such a query, but like you say, that’s question of good database design.

Reply | Quote

This kind of thing always puts us developers/consultants in a tough spot. The fundamental problem is that the original design is bad – you should have a linked table for the exam dates. And then the client says – no don’t fix the design, just solve my problem. You can probably solve it this time, but the more courses they add the worse it gets, and eventually the wheels fall off. Besides that, it’s pretty hard to just show the courses that have not been retaken when you have all the data in one record. Either you get that record or you don’t, and to create a useful report, you would normally only want the data that needs attention. That’s probably the best message to convey to them.

Actually, I’m not sure that the limit of 40 ANDs applies to OR statements – I haven’t tried it, but I don’t think the same constraints apply for ORs. I’m sure there is an upper limit but we’ve never come close to it. Good luck!

Reply | Quote

Thanks to all of you for your input on this one – I can now solve the immediate problem – but I agree I will have to persuade them into a major rethink!

Reply | Quote