Selecting Most Recent date of two catagories

[tritenick]

What works: I have a training database with 5 types of training. A most recent query sorts all training and feeds into different queries filtered by training type. This has been working great.

New twist. A new training has been added that can meet the requirement of one of the other trainings. Employees can take Advanced Safety Training or new General Safety traninng to meet the safety training requirements.

Problem: I need the safety training due query to pull the most recent date between the advanced and general safety. I added the trainings in the criteria for training as an OR but now it pulls both trainings rather than the most recent of the two.

Is there another way to compare the dates of different training to get the most recent of the two?

Thanks in advance!

 

[Rx_]

Trying to visualize this.
Is one training item Dependent on another training item? Perquisite?
For example: does MediumSafety require BeginningSafety?
Do you have a Perquisite field that hold the ID of the required course?

"can meet the requirement of one of the other trainings."
Or, do you have a "Meets Requirement Of" field that has the ID of the course it can meet?
EG:
ID----Course---Date----AlsoMeetsRequirementOf
1 ----Begining--1/2/2
2-----Medium---1/3/2----------1
3-----Crash-----1/4/2----------2

 

[tritenick]

RX,
Thanks for the reply. No dependency or sequence of completion necessary. They are all independent modules. Everyone is required to take a safety training. Some job functions require they have advanced, others only need general. Some people are taking both (for fun I guess).

For the id, added training is saved in tblTraining by an autonubmer that links to tblWorkers.

I run a filtered (by training type) query to see who is due for specific types of training by adding a year to the most recent training date and comparing it to todays date. Worked fine.

Just not when I am filter "advanced" OR "General", then I get due dates for oldest one.

 

[tritenick]

Quoting boblarson from another similar post (that I didn't find over several days of searching. Sorry.)

If you wanted the latest date over all, you just include that field in the query with the max. To get the latest date over, say UserID, then you would include UserID and MaxDate. Any other fields you would not use and then use a query to put that other query into and link up the user ID fields and the MaxDate field to the date field. But you can also do it as a subquery but I have a hard time explaining that.

This is the issue I had, I was using my most recent query to sort other fields. Once I removed all fields except EmployeeID and MaxDate, it worked like a charm. Thanks boblarson!