How to make a query select only the latest row? (1 Viewer)

BadBoy House

Registered User.
Local time
Today, 13:38
Joined
Oct 8, 2009
Messages
54
I have a query on my Access database which selects the Staff Reference, Hourly Rate and Hourly Rate Effective Date from a Staff table.

Each staff member gets a new hourly rate each year and therefore for each staff member there are mutiple rows containing details of the hourly rates for the previous year.

The query looks like this:

Staff Reference - Date - Rate
John Smith - 01/12/16 - 20.00
John Smith - 01/12/17 - 25.00
John Smith - 01/12/18 - 30.00
Jane Doe - 01/12/15 - 20.00
Jane Doe - 01/12/16 - 25.00
Jane Doe - 01/12/17 - 30.00

What I would like to do is program the query so that it only selects the most recent entry for each person..like this:

Staff Reference - Date - Rate
John Smith - 01/12/18 - 30.00
Jane Doe - 01/12/17 - 30.00

I was wondering if anyone could suggest the best way for me to do this?

Thanks in advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:38
Joined
Oct 29, 2018
Messages
21,358
Hi. Untested but maybe something along these lines:


SELECT Staff, Date, Rate
FROM StaffTable
INNER JOIN (SELECT Staff, Max(Date) AS LastDate
FROM StaffTable
GROUP BY Staff) AS SQ
ON StaffTable.Staff=SQ.Staff
AND StaffTable.Date=SQ.LastDate


Hope it helps...
 

BadBoy House

Registered User.
Local time
Today, 13:38
Joined
Oct 8, 2009
Messages
54
Thanks. I made sure that all the fields in my table matched the details in your code howeverI get a "Syntax error in FROM clause" message when I run the query.

Any ideas why?

As a test I used just the following details in the query:

SELECT StaffTable.Staff, StaffTable.Date, StaffTable.Rate
FROM StaffTable;

INNER JOIN (SELECT Staff, Max(Date) AS LastDate

However this then shows a message that says "Characters found after end of SQL statement)
 
Last edited:

BadBoy House

Registered User.
Local time
Today, 13:38
Joined
Oct 8, 2009
Messages
54
I figured it out - found the code below after some Googling and adapted it to my tables:

Code:
SELECT agent_code, ord_date, cust_code 
FROM orders S 
WHERE ord_date=(
SELECT MAX(ord_date) 
FROM orders 
WHERE agent_code = S.agent_code);


Thanks for the replies. Much appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:38
Joined
Oct 29, 2018
Messages
21,358
Hi. Congratulations. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom