Help grouping on last name and then findng latest date

[Guest]

My database contains records for treatment dates of patients. I am trying to
run a query that will bring back the last treatment date for each patient.
I am using office 2000.

 

[Allen Browne]

Setting up the correct tables will be the crucial aspect of your query.

One patient can have many treatments, so you need at least the 2 tables
below. Note that lots of people have the same surname, so you need to create
a PatientID to uniquely identify your patients and record their treatments.

Your Patient table will have these fields:
PatientID AutoNumber primary key
Surname Text
FirstName Text
Address Text
...

Treatment table fields:
TreatementID AutoNumber primary key
PatientID Number (foreign key to Patient.PatientID)
TreatmentDate Date/Time
...

Now you can create a query that includes both tables. In query design view,
depress the Totals button (upper sigma icon on toolbar). Access adds a Total
row to the query grid.

Drag the PatientID field into the grid, and accept Group By under this
field.
Drag the TreatmentDate field into the grid, and choose Max under this field.
The query returns each patient, and their most recent treatment date.