nested OR in queries

  • Thread starter Thread starter vgraybeal
  • Start date Start date
V

vgraybeal

I have a table that has 20 date fields for each record. I need to select any
record that has a matching date in at least one of those fields so it becomes
a huge OR in a query and I have noticed that I am limited to 9 in a query. Is
there a way to do this efficiently or will I need multiple queries to test
all 20 date fields?
 
If your table uses multiple date fields, the first thing you'll want to
consider is coming up with a new table!

In a spreadsheet, adding 20 date fields may be the only way to handle a
situation, but you won't get the best use of Access' features and functions
if you don't use well-normalized tables.

You didn't explain much about those 20 fields, but I'll hazard a guess that
they represent different steps or stages or types or categories of dates.
In a relational database (e.g., Access), you'd handle this with something
like:

tblSomethingDate
SomethingDateID
SomethingID (to what does the following date apply?)
YourDate (don't use "Date" as a field name-this is a reserved word
in Access)
YourDateTypeID (this is where you put the category you were using 20
fields to show)

If I've mis-interpreted, please post back with a more detailed description.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Sounds as if you are using QBE (Design view grid).

Click on any criteria row and then select INSERT Rows from the menu.

This will add an additional criteria row.

The problem here is that you have 20 date fields in one row of data. It is
probable that you should be storing these dates in another table that
contains the date you are storing, the type of date (DOB, Communion Date,
Date of Graduation, etc) and the primary key value from you present table.
With that structure all you would need is to join the two tables and place
criteria against one field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I have a table that has 20 date fields for each record.
Click to expand...

Then you have a spreadsheet, not a table. Your table design IS WRONG. If you
have a one to many relationship, you need to model it as a one to many
relationship between two tables! "Fields are expensive, records are cheap".
I need to select any
record that has a matching date in at least one of those fields so it becomes
a huge OR in a query and I have noticed that I am limited to 9 in a query. Is
there a way to do this efficiently or will I need multiple queries to test
all 20 date fields?
Click to expand...

You're not actually limited to 9 - if you insist on using this incorrect table
design, you *can* select one or more rows in the query grid and use the INSERT
ROWS menu option to add more rows. Or you can go into SQL view and edit the
SQL of the query to add multiple more OR's. At some point you'll get the
"Query Too Complex" error though - I'd really recommend that you correct your
table structure!

John W. Vinson [MVP]
 
Back
Top