Finding the most recent dated record on a table

[winston01]

Thank you in advance for your help.

I want to create a dateadd field in a query to display when a patient's next appointment is due. I need to find the most recent appointment date and add 6 months to it. I know how to use dateadd, but how do you tell the system to use only the most recent date in the appt date field when there are 40 appointments on the table for a given patient.

Thank you.

 

[lespaul]

A starting point:

SELECT PatientID, Max(AppointmentDate) FROM tableName GROUP BY PatientID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[winston01]

Currently, I have dateadd("m",6,[appointments]![appointmentdate]

The query shows a record for each appointment with the new date. I want one record to show with a due date based on 6 months from the last visit.

How exactly would I implement the above information with what I already have?

Thank you for all your help Leslie. I feel like I am about to pull my hair out. I appreciate your kindness.

 

[PHV]

What is the actual SQL code of the query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[winston01]

SELECT [pendingexams].[ptid], DateAdd("m",12,[findings_mammo]![date]) AS duedate, [pendingexams].[datescheduled], [pendingexams].[procedure], [pendingexams].[datecompleted]
FROM pendingexams INNER JOIN findings_mammo ON [pendingexams].[ptid]=[findings_mammo].[ptid];

Thank you!

 

[PHV]

Which field is the appointment date ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[winston01]

[findings_mammo]![date] is the appointment date

I want to automatically fill [pending exams]![duedate] which should display the due date of the next appointment to be 12 months from the last appointment date.

Thank you

 

[PHV]

what about this ?

SELECT P.ptid, DateAdd('m',12,L.LastAppt) AS duedate, P.datescheduled, P.procedure, P.datecompleted
FROM (pendingexams AS P
INNER JOIN findings_mammo AS F ON P.ptid=F.ptid)
INNER JOIN (
SELECT ptid, Max([date]) AS LastAppt FROM findings_mammo GROUP BY ptid
) AS L ON P.ptid=L.ptid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886