Date Logic Within Query

[utzja1]

I am trying to create a simple database tool to track some training dates for some inspectors in our company. The inspectors are required to take Class A one time and then take a "Refresher" class, Class B, every 3 years. I am trying to code a query that shows the next due date for Class B for each inspector. Some of our inspectors have only recently taken Class A.

I have one table of inspectors with all their employee identifiers. I have a separate table with a training log with each record showing an inspector taking a particular class and when they completed it.

The DATEADD function by itself is straightforward, and I can verbalize the logic but I am having a difficult time designing the query.

If the employee doesn't have Class B in their training log, then I DATEADD 36 months to the end date of their Class A. If they do have Class B in their training history, then I DATEADD 36 months to the end date of Class B. This is where I'm stuck, trying to develop the Iif statement that adds 36 months to the training date depending on whether or not Class B is in the training log.

Is this possible within the Query design window, or does this require a VBA module? I've tried several approaches but I seem to filtering my own results rather than implementing the logic I described above.

Thanks for your consideration.

 

[Ranman256]

In the query using the Existing record. (Max)

DateAdd("m",36,[DateTaken])

 

[utzja1]

Thanks for the response. I should probably clarify that our inspectors can take several other classes (call them Classes C, D and E) that do not have the same "refresher" requirement. Searching for the record with the Max date could possibly return a date for something other than Class A or B, could it not?