Another most-recent date query


Joined
Oct 8, 2012
Messages
1
Reaction score
0
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
 

Attachments

  • dbrelations.png
    dbrelations.png
    27.4 KB · Views: 173
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top