Selecting records with most recent date...

  • Thread starter Thread starter Jason Lambertus
  • Start date Start date
J

Jason Lambertus

I have a table that contains an item number field and a
date field.

The item number field is duplicated across a number of
records due to new inputs.

Is there a function I can use to display only the most
recent date for each unique item number?

Example:

10101 12-jun-03
10101 21-dec-03
10101 04-mar-04
10102 15-sep-02
10102 24-oct-03

I would like to create a query that will only display the
most recent record for each item number based on the date
it was entered.

Any thoughts?
 
If you only want to see the most recent date for each item number, you might
try a query whose SQL look something like this:

SELECT
[Your Table].[Your Item Number Field],
Max([Your Table].[Your Date Field]) AS [Most Recent Date]
FROM
[Your Table]
GROUP BY
[Your Table].[Your Item Number Field]

If there are other fields in your table whose values you want to see from
the record with the most recent date, you might try a query whose SQL look
something like this:

SELECT
[Your Table].[Your Item Number Field],
[Your Table].[Your Date Field] AS [Most Recent Date],
[Your Table].[Your Other Field 1],
[Your Table].[Your Other Field 2]
FROM
[Your Table]
WHERE
[Your Table].[Your Date Field] =
(SELECT
Max([Self].[Your Date Field])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Your Item Number Field] = [Your Table].[Your Item Number Field])

You might refer to

http://www.mvps.org/access/queries/qry0020.htm

for a discussion of this and other approaches.
 
Back
Top