Query to display only the most recent records.

C

Cam

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.
 
D

Dale Fye

Cam,

Assuming you are using A2K or newer, try the following. The sub-query
will identify that record for each serial number that identifies the
maximum calibration due date. You then join that to your original
table by serial number and calibration due date, then filter it for
those records where the max calibration date is less than today.

Select *
From yourTable T
Inner Join (SELECT Serial, MAX(NextCalDate) as MaxCalDate
From yourTable
Group By Serial) as T1
ON T.Serial = T1.Serial
AND T.NextCalDate = T1.MaxCalDate
Where T.NextCalDate < Now()

I think the following would get you the same result set. The only
difference is that in the first one, the subquery will return a record
for each serial number, and the WHERE clause is accomplished after the
join. In the second one, the subquery will only return those serial
numbers that are out of calibration. In both cases, you still need to
do the join to the original table to get the other information that is
specific to the most recent record for that serial number.

Select *
From yourTable T
Inner Join (SELECT Serial, MAX(NextCalDate) as MaxCalDate
From yourTable
Group By Serial
Having Max(NextCalDate) < Now()) as T1
ON T.Serial = T1.Serial
AND T.NextCalDate = T1.MaxCalDate

--
HTH

Dale Fye


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.
 

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