By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Microsoft Query - How To Get The Most Recent Date

P: 1
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
Jun 14 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,324
Use the MIN() function and group by the ID.
Jun 14 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.