Max Of query pulling duplicates (1 Viewer)

Hello,

I realise this is a fairly common issue and have found some help on google, however the reasing/code behind the answer is fairly confusing to me unfortunately.

I’m trying to create a query to pull a list of all patients’ most recent appointments. I’ve a base query of ‘active patients’ (alive, not discharged to another hospital, etc) upon which I run all other queries, including this one.

This is what the ‘ActivePatient’ query looks like

ApptID Patient ID Appointment Date Attended? Follow Up Date
1 1 01/02/2017 Yes 01/02/2018
2 2 01/02/2017 Yes 01/02/2018
3 2 15/09/2016 Yes 01/02/2017
4 2 15/07/2016 No 15/09/2016
5 3 21/02/2017 No 21/02/2018
6 3 13/01/2017 Yes 21/02/2017
7 3 14/08/2016 No 13/01/2017
8 3 02/03/2016 No 14/08/2016

When I run the ‘most recent appointment’ query I get;

ApptID Patient ID Appointment Date Attended? Follow Up Date
1 1 01/02/2017 Yes 01/02/2018
2 2 01/02/2017 Yes 01/02/2018
4 2 15/07/2016 No 15/09/2016
5 3 21/02/2017 No 21/02/2018
6 3 13/01/2017 Yes 21/02/2017

…but what I actually want is…

ApptID Patient ID Appointment Date Attended? Follow Up Date
1 1 01/02/2017 Yes 01/02/2018
2 2 01/02/2017 Yes 01/02/2018
5 3 21/02/2017 No 21/02/2018

Heres the SQL code;

“
SELECT qyrPatientsActive.Patient ID, qyrPatientsActive.Surname, qyrPatientsActive.[First Name], Max(tblAppointments.[Appointment Date]) AS [MaxOfAppointment Date], tblAppointments.[Attended?]
FROM qyrPatientsActive INNER JOIN tblAppointments ON qyrPatientsActive.Patient ID = tblAppointments.Patient ID
GROUP BY qyrPatientsActive.Patient ID, qyrPatientsActive.Surname, qyrPatientsActive.[First Name], tblAppointments.[Attended?]
ORDER BY qyrPatientsActive.Surname;

“

Thanks guys

when you run a MAX query ,you can only have 2 fields,
the ID ,and Max(field).
otherwise max function pulls max of everything in the record, SO...

make a query using the ActivePatient query, (call this new query: qsMaxActive)
pull ONLY: ID ,and Max([Appointment Date Attended]).

now you can keep this, but if you want more info, you need another query:
make new query,
join qsMaxActive to the tPatient table to get names to go with the Dates.

Ranman256 said:

when you run a MAX query ,you can only have 2 fields,
the ID ,and Max(field).
otherwise max function pulls max of everything in the record, SO...

make a query using the ActivePatient query, (call this new query: qsMaxActive)
pull ONLY: ID ,and Max([Appointment Date Attended]).

now you can keep this, but if you want more info, you need another query:
make new query,
join qsMaxActive to the tPatient table to get names to go with the Dates.

Click to expand...

thanks for the simple explanation. I'll give it a go and let you know how I get on

so qsMaxActive query works fine, but when I go to join it with tblPatients, it is again pulling all appointment records (around 11,000). Is it the type of join? Would an outer join be better?

It should pull the exact # in qsMaxActive, joined on ID.

If I add in just surname, forename, etc, yes it works fine. However I want to be able to see if that one appointment was attended or not attended. If I add that in, it pulls all records.

EDIT: sorry I should have originally mentioned that being able to see whether that appointment was attended or not was important.

well there you go. adding a table with multi records, will produce duplicates.

so there is no way to bring up other fields from a multi-record table when running a max of date query from that table without pulling all records?

rather than using the table for your names, use a query

SELECT DISTINCT ID, firstname, lastname
FROM tblPatients