473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Microsoft Query - How To Get The Most Recent Date

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
1 1737
Rabbit
12,516 Expert Mod 8TB
Use the MIN() function and group by the ID.
Jun 14 '12 #2

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

Similar topics

6
by: HandersonVA | last post by:
There are several day_timestamp for each index_id. Anyone can help me to write a sql to generate the most recent day_timestamp of index_ids which has not accessed into the system in 90 days from...
1
by: justinjoylife | last post by:
Hi - I'm completely new to Microsoft Query Analyzer and I need to learn it for work to do data mining as a Product Manager. Does anyone have any recommendations on how to learn this and where?...
3
by: | last post by:
I have a collumn filled with dates. How would I go about selecting the record with the most recent date. using access 2000. thanks!
3
by: William Wisnieski | last post by:
Hello Everyone, I'm helping out a non-profit school with their database. They would like to know the last gift made by each donor, the donor name, and the gift amount. I built a query based...
1
by: pht204 | last post by:
Hi there, I have a list of date on my table: ClientName ClientPurchase PurchaseDate PuarchesItems Let's say, the purchas dates are multiples and I want to get each clients with their most...
2
by: gafchic | last post by:
I manage a training database where I work and I would like to run a make table query. The table I want to query has a list of trainings our employees have taken and the dates they have taken the...
1
by: jomurf | last post by:
Not sure if this is the right forum. I'm using Microsoft Query to get data from our ERP. However, every time I try to sum a column, I get a syntax error. Here's the sql statement: SELECT...
1
by: Proaccesspro | last post by:
I have 2 tables in a query. One of the tables contains a field titled Action Date. How can I query for the most recent date in the field Action Date? Dmax?
3
by: shivasusan | last post by:
Hi! I am using for my project ASP and Microsoft Access. start date and end date -- In database (Microsoft Access) i set the datatype is Date/Time. It's display error. The error is below:...
3
by: Ginny28 | last post by:
I am running the following query and need to modify it to pull only 1 row, with the most recent Onset Date, when the pr.diagnosis_code_id and pr.description are not unique. I could actually use one...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.