Setting query criteria based on a field in the query

[scooteman]

I am setting up a database to track employee training and I am not sure how to setup two queries that I need. One to search for training that is out of date and another for training that is coming due in the next 60 days.

The fields I am looking at are [Training_Cycle] which can be Once, Annual, or 3 years and [Date_Tained]. I need for my query to look at the [Training_Cycle] first and then the [Date_Trained] field to determin if training is past due.

Once = Null for the search criteria
Annual = greater than a year from the current date
3 years = greater than three years from the date.

I can creat simple queries but this in beyond what I can do. I have Access 2007

Any help would be greatly appreciated!

 

[jal]

As for the first query, a sketch might look something like this. Assuming your training table is called Table1

SELECT * FROM Table1 WHERE [Training_Cycle] = "Once" AND [Date_Trained] IS NULL

UNION ALL

SELECT * FROM Table1 WHERE [Training_Cycle] = "Annual" AND DateDiff("d", [Date_Trained], Date()) > 365

UNION ALL

SELECT * FROM Table1 WHERE [Training_Cycle] = "3 years" AND DateDiff("d",[Date_Trained], Date()) > 3 * 365

 

[georgedwilkinson]

You could also replace the "UNION ALL" with "OR", dropping the extra "SELECT * FROM TABLE1 WHERE". Make sure to put parenthesis around all 3 sets of criteria, though.

 

[scooteman]

Thanks for your help!!!!