Selecting records with most recent date...

[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?

 

[Brian Camire]

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.