Query - searching dates on 3 separate columns with OR.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three columns in one table, each listing the expiration date of a
professional license in three different states. Some people have licenses in
all three states. Others only two or one state. I am trying to run a query
to list all licenses that expire before a certain date. I am using the
standard OR example. If the date in the second column satisfies the
criteria, the output also lists a date in the first column for that person
even if it is out of range. The same applies to the third column but lists
dates in column one and two even if out of range. How do I supress the out
of range dates in the previous columns?
 
Your problem is improper table design. You have a 1-many relationship
between license and state (last count there were 50, not 3). Information on
the license for a state belongs in a separate table. That way you can have
as many as necessary rather than limit people to three and your queries will
work better since you will only get the data you want.

If you can't fix the design immediately, you can simulate proper table
design with a union query. Use a query that unions three queries - one
query for each of the state columns. You will end up with a normalized
recordset.
 
Back
Top