How to extract latest record

R

Rene

I have a table with following fields:-
[Repair ID]; [Date]; [Location]

I need to extract the latest/newest LOCATION for each repair based on newest
date per repair. I have been sitting with this for some time now and just
cant seem to find the answer...

I thank you in advance for all your assistance!!!
 
R

Rick Brandt

Rene said:
I have a table with following fields:-
[Repair ID]; [Date]; [Location]

I need to extract the latest/newest LOCATION for each repair based on
newest date per repair. I have been sitting with this for some time
now and just cant seem to find the answer...

I thank you in advance for all your assistance!!!
Click to expand...

SELECT A.[Repair ID], A.Location
FROM TableName A
WHERE (((A.Date) In (SELECT Max(B.Date) FROM TableName B WHERE B.[Repair ID]
= A.[Repair ID])));
 
R

Rick Brandt

Rick said:
Rene said:
I have a table with following fields:-
[Repair ID]; [Date]; [Location]

I need to extract the latest/newest LOCATION for each repair based on
newest date per repair. I have been sitting with this for some time
now and just cant seem to find the answer...

I thank you in advance for all your assistance!!!
Click to expand...

SELECT A.[Repair ID], A.Location
FROM TableName A
WHERE (((A.Date) In (SELECT Max(B.Date) FROM TableName B WHERE
B.[Repair ID] = A.[Repair ID])));
Click to expand...

I should have added that "Date" is a reserverd word in Access and as such
should never be used as the name of a field or object. Change to RecordDate
or similar.
 
Top