How to select first record based on a field bu include auto gen PK in results.

[stevo6mm]

Hi there.

I have a table with data as follows:

ID |Name |Role |Start Date


----------------------------------
1 |Mr A |Role 1 |01/01/2012
2 |Mr B |Role 1 |01/02/2012
3 |Mr C |Role 2 |01/01/2012
4 |Mr D |Role 2 |01/02/2012
5 |Mr E |Role 3 |01/01/2012
6 |Mr F |Role 3 |01/02/2012

Where ID is an auto-generated number.

I am trying to select only the most recent entry for each role. I need to ensure I get the ID field in the result as I need to process the record further. The 'Group By - First' feature in access query builder is not suitable as it treats the ID of each reacord as a unique identifier and hence returns every record in the table.

Your help appreciated.

Regards

Steve

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

[stevo6mm]

Yep. I reckon that'll do nicely. Thanks very much.

Steve

[pbaldy]

No problem, and welcome to the site by the way.