Query to return latest records

[music_al]

I have a query which lists many date related events for 3 or 4 people. Like this...

Date Person
22/5/09 Mr Smith
17/5/09 Mr Smith
12/5/09 Mr Smith
21/5/09 Mrs Jones
16/5/09 Mrs Jones
11/5/09 Mrs Jones

I only want the query to return the LATEST date for each person as highlighted in red. How do I do this ?

Thanks

Al

 

[dcb]

Your looking for First Function

SELECT Table.Person, First(Table.YourDate) AS LatestDate
FROM Table
GROUP BY Table.Person
ORDER BY First(Table.YourDate) DESC;

 

[music_al]

Thanks for that but Im a bit of a beginner to queries. Where would I enter this ?

 

[dcb]

In the design pane : hell im not good at this
I will mock up and take a print screen
Give me a sec

 

[dcb]

Have a look at this using the designer

Cheers

 

[music_al]

I think the problem is, when I go into my design view, it has a LONG SQL Statement and Im having problems understanding where to put the functions

Here is my SQL Statement...

SELECT tbl_Appointment.Appointment_ID, FIRST(tbl_Appointment.Appt_Date) as latestdate, tbl_Appointment.NHS_No, tbl_Nurse.[Nurse Name], tbl_Nurse.[Nurse Initials], tbl_Nurse.Nurse_Telephone, tbl_Nurse.Nurse_Fax, tbl_Nurse.Nurse_Email, tbl_Patient.Pnt_Title, tbl_Patient.Pnt_First_Name, tbl_Patient.Pnt_Surname, tbl_Patient.Pnt_Address1, tbl_Patient.Pnt_Address2, tbl_Patient.Pnt_Street_Road, tbl_Patient.Pnt_Town_City, tbl_Patient.Pnt_County, tbl_Patient.Pnt_Post_Code, tbl_Patient.DOB, tbl_Appointment.Assessing_Nurse, tbl_Appointment.Diagnosis, tbl_Appointment.LTOT_Prev_Flow_Rate, tbl_Appointment.LTOT_Prev_Hours, tbl_Appointment.LTOT_Prev_Equipment, tbl_Appointment.LTOT_Previous_Device, tbl_Appointment.Follow_Up_Date, tbl_Appointment.Additional_Notes, tbl_Appointment.Medication_Change_Notes, tbl_Appointment.Additional_Investigation_Notes
FROM (tbl_Patient INNER JOIN tbl_Appointment ON tbl_Patient.[NHS_No] = tbl_Appointment.[NHS_No]) INNER JOIN tbl_Nurse ON tbl_Appointment.Assessing_Nurse = tbl_Nurse.Nurse_ID
GROUP BY tbl_Appointment.Appointment_ID, tbl_Appointment.Appt_Date, tbl_Appointment.NHS_No, tbl_Nurse.[Nurse Name], tbl_Nurse.[Nurse Initials], tbl_Nurse.Nurse_Telephone, tbl_Nurse.Nurse_Fax, tbl_Nurse.Nurse_Email, tbl_Patient.Pnt_Title, tbl_Patient.Pnt_First_Name, tbl_Patient.Pnt_Surname, tbl_Patient.Pnt_Address1, tbl_Patient.Pnt_Address2, tbl_Patient.Pnt_Street_Road, tbl_Patient.Pnt_Town_City, tbl_Patient.Pnt_County, tbl_Patient.Pnt_Post_Code, tbl_Patient.DOB, tbl_Appointment.Assessing_Nurse, tbl_Appointment.Diagnosis, tbl_Appointment.LTOT_Prev_Flow_Rate, tbl_Appointment.LTOT_Prev_Hours, tbl_Appointment.LTOT_Prev_Equipment, tbl_Appointment.LTOT_Previous_Device, tbl_Appointment.Follow_Up_Date, tbl_Appointment.Additional_Notes, tbl_Appointment.Medication_Change_Notes, tbl_Appointment.Additional_Investigation_Notes
ORDER BY FIRST(tbl_Appointment.Appt_Date) DESC;

 

[dcb]

Ah - you need to do two querys here
PS which version of Access are you using?

 

[music_al]

Im using 2007

I need to get the data into one query so I can refer to it in a mail merge document.

Are you suggesting I insert a query into another query ?

 

[dcb]

Could you take a Print Screen of your relationships? Or if the DB is empty upload it?

 

[dcb]

Im using 2007

I need to get the data into one query so I can refer to it in a mail merge document.

Are you suggesting I insert a query into another query ?

Yip
Basically to define a ID inOne query and limit the Main query using that

 

[music_al]

there ya go...

 

[dcb]

Is NHS_NO Unique to the patient or Visit? I ask cause this looks like a intermediate join?
Will one patient always have the same NHS_NO?

We dont have NHS where I live - or rather its "have a paracetemol"

 

[music_al]

NHS_No is unique to each patient an yes, the NHS Number is with them for life...

 

[dcb]

Wow - I must have been drinking way too much coffee last night
I have mislead you with the "First" Statement - Should be "Max"

Sorry about that

Heres a working sample - have a look

 

[music_al]

Thats great - I will use that

)