query filtering

[maverickmonster]

I have a table with lots of information but importantly with a URN (Unique reference number) and a date.

I want to take the latest date for each member how can i do this via a query ?

 

[PHV]

Something like this ?
SELECT URN, Max([date field]) AS LastDate
FROM yourTable
GROUP BY URN;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maverickmonster]

Yeah thats good but if i try to add more fields then the error comes of

"you tried to execute a query that does not include the specified express "Name" as part of an aggregate function"

How can i add extra fields ?

 

[PHV]

SELECT A.*
FROM yourTable AS A INNER JOIN (
SELECT URN, Max([date field]) AS LastDate FROM yourTable GROUP BY URN
) AS B ON A.URN = B.URN AND A.[date field] = B.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maverickmonster]

Spot on thanks mate