Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi field 'Or' Query (access 2000)

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi field 'Or' Query (access 2000)

    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi field 'Or' Query (access 2000)

    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.
    Attached Files Attached Files
    Francois

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multi field 'Or' Query (access 2000)

    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!
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi field 'Or' Query (access 2000)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •