'MaxOf' Query returning duplicates (1 Viewer)

I've two tables, tblPatients where the primary key is PatientID, and tblAppointments where the primary key is AppointmentID;

I've multiple appointment entries for each patient, as well as a follow up date for when the next appointment is to be booked. When trying to create a 'max of' query for appointment date, the query is returning 2 appointment records for each patient record; one is the max of appointment with the follow up date field empty, and the other is the max appointment date with the follow up date not empty. I want to return the max of appointment regardless of follow up date, but i need to be able to see what the follow up date is in the query result.

Thanks for your help!


SQL:

SELECT tblAppointments.PatientID, Max(tblAppointments.AppointmentDate) AS MaxOfAppointmentDate, tblAppointments.FollowUpDate
FROM tblAppointments
GROUP BY tblAppointments.PatientID, tblAppointments.FollowUpDate
ORDER BY tblAppointments.PatientID;

You need to do the aggregate query without the Followup date first then join that query back to the table again to pick up the followup date.

Galaxiom said:

You need to do the aggregate query without the Followup date first then join that query back to the table again to pick up the followup date.

Click to expand...

Thanks for the quick reply. Not really sure how to join the query back to the table though?

On your Select statement, use Max(tblAppointments.FollowUpdate)
then ONLY group by PatientID.

arnelgp said:

On your Select statement, use Max(tblAppointments.FollowUpdate)
then ONLY group by PatientID.

Click to expand...

Is that instead of Max(tblAppointments.AppointmentDate)? Would I then need to delete tbl.Appointments.FollowUpDate from the SELECT statement?

Yes u should

arnelgp said:

Yes u should

Click to expand...

So the SQL would then look like this;

SELECT tblAppointments.PatientID, Max(tblAppointments.FollowUpDate) AS MaxOfAppointmentDate
FROM tblAppointments
GROUP BY tblAppointments.PatientID ?

Doesn't quite seem right to me

sorry did not read thoroughly your last post.
you add also Max(tblAppointmets.AppointmentDate) As MaxAppointmentDate on your query.