Howto make Delete query that keeps most recent

[PSI]

I have the following (scaled down ) tables:

tblPatient
======
PatientID - Autonumber PK
fldName - text

tblVisits
=======
ID - Autonumber PK
PatientID - FK
DateofVisit - Date field

I would like to build a Delete query that removes all but the most
recent visit record for each Patient.

I think I need to do some kind of Max(DateOfVisit) AS MostRecentVist
combined with a WHERE (DateOfVisit < MostRecentVisit ) but not sure
how to do it.

Any help setting this up would be appreciated.

Thanks
Frank

 

[Ivar Svendsen]

tblVisits

=======
ID - Autonumber PK
PatientID - FK
DateofVisit - Date field

I think that this query should do the trick:

DELETE FROM tblVisits AS Old WHERE NOT EXISTS (SELECT Max
(Rescent.DateofVisit) AS rscDate FROM tblVisits AS Rescent
WHERE Rescent.PatientID=Old.PatientID HAVING Max
(Rescent.DateofVisit)<>Old.DateOfVisit);

Access will execute the inner query for each record, and
delete the record if the inner query does not return any
records.

The inner query calculates the Max visiting date,
restricting to the patient ID of the record ID being
deleted. It also checks that the date of the record being
deleted is different than the most rescent date for that
patient.



Regards,
Ivar Svendsen

 

[PSI]

Hello Ivar:

I tried your Query but it also removed records where there was only
one visit. However I was able to follow your example and came up with

DELETE
FROM tblVisits AS Old
WHERE EXISTS (
SELECT Max(Recent.DateOfVisit) AS rscDate
FROM tblVisits AS Recent
WHERE Recent.PatientID=Old.PatientID
HAVING Old.DateOfVisit< Max(Recent.DateOfVisit) );

which seems to do the trick.

I don't understand why the inner Query does the
Max(Recent.DateOfVisit) twice - once in the SELECT and once in the
HAVING, and the rscDate never gets used anywhere. ( I blindly tried
to use rscDate in the HAVING clause - but of it did't work
(prompted for the value )). If you have the time, a brief
explanation would help my understanding.

Thanks very much for the help!
Frank

 

[Ivar Svendsen]

I don't understand why the inner Query does the

Max(Recent.DateOfVisit) twice - once in the SELECT and once in the
HAVING, and the rscDate never gets used anywhere. ( I blindly tried
to use rscDate in the HAVING clause - but of it did't

When using a group query, Access cannot reference to an
output alias, only to fields and functions of the input
tables. Therefore it writes the Max() expression twice,
because it do not know about the output alias (rscDate).

However, someone has explained to me that Access keeps
the expression in an internal cache, reusing it instead
of doing the same calculation twice.


Regards,
Ivar Svendsen

 

[PSI]

I'm using Jet/C++. I'm curious whether the caching would be part of
Access or hopefully the Jet engine.

Thanks for the help.
Frank