Microsoft Query - How To Get The Most Recent Date

[neopro]

Hi All,

I am a novice when it comes to using Microsoft Query and need some assistance.

Essentially, I am querying from a list of certificates stored in Microsoft CRM and each certificate is associated to a practitioner. Note that each practitioner can have several certificates with different lodgement dates.

What I need to do is retrieve the most recent certificate lodgement date for each practitioner.

Here is an example:

Practitioner ID | Certificate Number | Certificate Lodgement Date
1 | 2 | 1/01/2012
2 | 4 | 1/01/2012
3 | 5 | 1/04/2012
4 | 6 | 3/02/2012
1 | 8 | 4/03/2012
2 | 9 | 2/02/2012
3 | 11 | 15/04/2012
4 | 14 | 4/03/2012
1 | 16 | 2/04/2012
2 | 19 | 3/04/2012
3 | 22 | 2/05/2012
4 | 24 | 15/03/2012
1 | 25 | 2/04/2012
2 | 30 | 5/06/2012
3 | 32 | 2/05/2012
4 | 40 | 20/05/2012


The query results that I expect are:

Practitioner ID |Certificate Lodgement Date
1 | 2/04/2012
2 | 5/06/2012
3 | 2/05/2012
4 | 20/05/2012

I am not sure of the SQL codes specific to Microsoft Query that can get the job done.

Thank you in advance.

Regards
NeoPro

[Rabbit]

Use the MIN() function and group by the ID.