Query Update/Select Query (Wont Update)

[NickWren]

I have 2 tables, one table that will have basic Patient information and the second has the Sample Information

tblPatients:
-PatientID (PK)
-PatientName
-DOB
-Ethnicity
-Sex
-Location
-...
-DateOfLastTest (Needs to update from Update Query)

tblSampleStatus:
-SampleID (PK)
-PatientID (FK)
-DateOfTest (What needs to be put into [tblPatients]![DateOfLastTest]
-Test1Score
-Test2Score

qryLastestTestInfo:
SELECT tblPatient.PatientID, Max(tblSampleStatus.DateReceived) AS MaxOfDateReceived
FROM tblPatient INNER JOIN tblSampleStatus ON tblPatient.PatientID = tblSampleStatus.PatientID
GROUP BY tblPatient.PatientID;

Pretty much gives me a PatientID and DateOfLastTest

qryPatientFollowUP:
SELECT tblPatient.PatientID, tblPatient.PatientName, tblPatient.PatientPhoneNumber, tblPatient.PatientEmail, tblPatient.PatientAddress, tblPatient.City, tblPatient.State, tblPatient.ZipCode, tblPatient.RegistrationDate, tblSampleStatus.DateReceived, tblPhysicians.PhysicianFullName, tblPhysicians.PhysicianPhoneNumber, tblPhysicians.FacilityFullName, tblPhysicians.FacilityAddress, tblPhysicians.FacilityCity, tblPhysicians.FacilityStateAbbreviation, tblPhysicians.ZipCode, tblPhysicians.FacilityFax, tblPatient.LastTestDate
FROM tblPhysicians INNER JOIN ((tblPatient INNER JOIN tblSampleStatus ON tblPatient.PatientID = tblSampleStatus.PatientID) INNER JOIN tblPhoneLog ON tblPatient.PatientID = tblPhoneLog.PatientID) ON tblPhysicians.PhysicianLastName = tblPatient.PhysicianLastName;

This is an Update Query that will take the [tblSampleStatus]![DateOfTest] and input it in for [tblPatient]![DateOfLastTest];


however I keep getting an error that says: "Operation must use an updateable query."
I figure its the query I use to get the max date, but how else would I go about doing this?

Also this query will show multiple of the same patient, however I want it to only show the patient once, how can I fix that.

[JoeM]

Yes, you cannot use an Aggregate Query in any part of an Update Query.
But the question is, do you really need to store the Date of the Last Test in a table?
Anything that can be calculated "on-the-fly" usually does not need to be stored at the Table level (and probably shouldn't be, as it usually violates Rules of Normalization and can compromise database integrity).