Why is this query not updatable?

[John S. Ford, MD]

I have a query based on the following tables and queries:

tblAdmissions, a table with primary key AdmissionIDNum and fields relating
to hospital admissions such as PatientIDNum, PatientAdmitDate,
PatientDischargeDate, etc. Each record corresponds to one exactly one
admission.

For each admission, there will be one or more attendings taking care of that
patient (patients can be transferred sequentially from one attending to
another). I therefore have a tblLINK_AdmissionAttending with a
one-to-many relationship linked by AdmissionIDNum which has additional
fields such as AttendingStartDate and AttendingEndDate.

I created a query (qryLastAttending) based on tblLINK_AdmissionAttending
using the DMax function that yields a single record for each admission and
provides the LAST attending for that admission (i.e. the attending for each
admission with the latest AttendingStartDate.

I then joined tblAdmissions with qryLastAttending using AdmissionIDNum as
the join field.

tblAdmissions, qryLastAttending and the resulting joined query are all
updateable.

Now in addition to having attendings sequentially take over the care of each
admitted patient, residents also sequentially care for the same patient. I
created a qryLastResident using the same technique described above and
linked it to the tblAdmissions (which was already joined by
qryLastAttending).

The resultant query IS NO LONGER UPDATEABLE. Does anyone know why and how I
can fix this?

John

 

[Jeanette Cunningham]

Hi John,
here is a link that explains why
http://allenbrowne.com/ser-61.html

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John S. Ford, MD]

Jeanette,

I've looked through lists such as the one you linked. The closest I've been
able to come to understanding the reason in my case has to do with the DMax
function in qryLastAttending and qryLastResident. However each of these
queries by themselves IS updateable and linking either one by itself to
tblAdmissions yields an updateable query. Only when I link BOTH of them to
tblAdmissions do I get a non-updateable query.

John

 

[Jeanette Cunningham]

Sometimes you can workaround the problem by using a subquery.
Perhaps replace the DMax with a subquery that uses the most recent date - no
guarantees that will work, but sometimes it helps.
If a query is not updateable, I find that searching for the reason is less
useful than trying something different.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John S. Ford, MD]

Jeanette, what do you mean "replace the DMax with a subquery"?

John

 

[Jeanette Cunningham]

Subqueries are a new thing to learn, it takes some time but it is worthwhile
for the flexibility you get.

Here is a link on getting started with sub queries.
http://allenbrowne.com/subquery-01.html

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia