Another most-recent date query

[lunacpm]

Hi,

I have a database used to track calibration due dates on a number of pieces of equipment. Each item has a basic record of information including the calibration frequency in one table. In a second table, records are added about each calibration event with the date performed.

I've made a query to pull data about each item, and its calibration frequency from the base info table, and the last date of calibration from the calibration events table. I'm trying to limit the results to only the most-recent calibration event, and then perform an expression to add the frequency to that date, resulting in the next due date. Unfortunately, I can't seem to make it ignore old calibration records, and it keeps presenting due dates that have passed in addition to actual upcoming ones.

Here is the SQL I have so far:

SELECT Equipment.[CPM#], Equipment.Description, Equipment.[Facility/Location], Equipment.[In-House Calibrated], Equipment.[Calibration Frequency], Max(Calibration.[Calibration Date]) AS [MaxOfCalibration Date], Max([Calibration Date]+[Calibration Frequency]) AS [Calibration Due], Calibration.[Notes/Comments]

FROM Equipment LEFT JOIN Calibration ON Equipment.[CPM#] = Calibration.[CPM#]

GROUP BY Equipment.[CPM#], Equipment.Description, Equipment.[Facility/Location], Equipment.[In-House Calibrated], Equipment.[Calibration Frequency], Calibration.[Notes/Comments], Equipment.[Retired Y/N]

HAVING (((Equipment.[Calibration Frequency]) Is Not Null) AND ((Max(Calibration.[Calibration Date])) Is Not Null) AND ((Equipment.[Retired Y/N])<>Yes));

I've attached a screenshot of the table relationships as well.

I'm not very familiar with SQL but I can muddle through with good examples.

Thanks for your help in advance,
-Diana