Plulling through one record where mulitple records exist in a table in a table

Mcgrco

Registered User.
Local time
Today, 08:35
Joined
Jun 19, 2001
Messages
118
I need to be able to pull one record where multiple records exist in a table in a table. Basically where more than one record exists I want to take the record with the furthest date in the future based on a date field in the table.

Any help is appreciated
 
I came a across a similar thing just yesterday, took me ages to work out but it actually quite easy!

I did it in SQL Server, you should be able to do it in an Access Query though.

SELECT [ID], MIN([Date]) AS MinDate
FROM

GROUP BY [ID]

(You can use MAX() as well)

Save that query, then make another to link the rest of your fields in

SELECT *
FROM

RIGHT OUTER JOIN [yourQuery]
ON [yourQuery].[ID] =
.[ID]
AND [yourQuery].[Date] =
.[Date]

Give me a shout if you can't get it to work or need any help with the SQL

hth
 
Cheers thats the trick.

Much appreciated.
 

Users who are viewing this thread

Back
Top Bottom