Display Overdue reports

  • Thread starter Thread starter Lu
  • Start date Start date
L

Lu

Hello,

I have a calibration database to track all tool
calibration data. The key field is an autoNumber. I would
like to create a report/query that show all the tools that
are overdue for calibration or past the next calibration
date. The problem I ran into is when I put <Now() in the
NextCalDue field, it'll not show the NextCalDue that are
scheduled greater than today, but it'll show all the past
records with the NextCalDue less than today. I don't want
to show past records.

Is there a way where I can filter out so it only show the
latest record (date) based on a Serial#?

Example:
# Serial CalDate NextCalDue
2 CD1522 8/20/01 8/20/02
42 CD1522 8/27/02 8/27/03
79 CD1522 9/01/03 10/01/04

I want it to not show past history data #2 and 42. Filter
out so it only consider the latest CalDate or NextCalDue.

Thanks for any help or suggestion.
 
You can create a query to display the most recent records with:
SELECT tblA.*
FROM tblA WHERE ID = (SELECT TOP 1 ID FROM tblA A WHERE A.Serial =
tblA.Serial ORDER BY CalDate Desc)
 
Hi Duane,

Can you put this in actual query view? I am confused with
your suggestion below. Thanks
 
The query design view doesn't support subqueries. You could start a regular
query and add the subquery to the criteria:
(SELECT TOP 1 ID FROM tblA A WHERE A.Serial =
tblA.Serial ORDER BY CalDate Desc)
 
Back
Top