BadBoy House
Registered User.
- Local time
- Today, 22:14
- 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
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