Only Return Most Recent Record

[Abraxus]

I have a table of phone numbers and vendor numbers along with an effective date. The data looks kind of like this:

Code: Select all

Vendor#   PhoneNumber     EffectiveDate
12345     816-555-1212    1/1/2010
12345     816-555-1213    2/10/2010
12346     816-222-1212    11/1/1990
12347     816-222-2222    4/11/1999
12347     816-222-5555    4/1/2000

I only want the records which have the most recent EffectiveDate, so I'd only want to return:

Code: Select all

Vendor#   PhoneNumber 
12345     816-555-1213   
12346     816-222-1212   
12347     816-222-5555  

How would I manage that in an Access query?

Thanks!

[HansV]

You need to create two queries:

1) A totals query that returns the most recent date for each vendor:

SELECT [tblSomething].[Vendor#], Max(EffectiveDate) As MaxDate FROM tblSomething GROUP BY [Vendor#]

where tblSomething is the name of the table. Save this as (for example) qryMaxDate.

2) A query based on the table and the query, joined on the vendor and on effective date vs max date. Return the vendor and phone number from the table:

SELECT [tblSomething].[Vendor#], [tblSomething].[PhoneNumber] FROM tblSomething INNER JOIN qryMaxDate WHERE [tblSomething].[Vendor#]=[qryMaxDate].[Vendor#] AND [tblSomething].[EffectiveDate]=[qryMaxDate].[MaxDate]

[Abraxus]

Perfecto!

Thank you very much.