Recordset Not Updateable message

I am somewhat new to Access and am working on a form for a doctor that
will be used to gather patient data. There are three linked tables:
Patients, Visits, and PatientAddresses. The patient table's PatientID
key is related in a one to many relationship to the visits table's
v_PatientID foreign key. This has worked well in relating the patient
data to the visits data. The doctor then added the idea of keeping a
"historic" listing of patient addresses, keeping the address at the
time of the visit associated with that visit through a numeric
identifier. So, my thought was to add a PatientAddresses table with a
pa_PatientID foreign key. However, in setting up the query for the
form, once the form is run, even though all data displays ok, new data
cannot be input. The "Recordset Not Updateable" message appears.

Here is the SQL for the form query:
SELECT Patients.*, Visits.*, PatientAddresses.*
FROM (Patients LEFT JOIN Visits ON Patients.p_ID = Visits.v_PatientID)
LEFT JOIN PatientAddresses ON Patients.p_ID =
PatientAddresses.pa_PatientID;

I'm wondering if a separate form for Patient Addresses would help this
situation (if the problem is too many tables in one query)? If
Patient Addresses data could populate (admittedly redundant) fields in
the Visits table, then would that be workable?

Thanks for any help or resources in this,
Louis

I'm wondering if a separate form for Patient Addresses would help this
situation (if the problem is too many tables in one query)?

Yes. Multi-table queries like that are usually not updateable. Typically, you
would use separate queries for each table, then use a Main form / Sub form
for Patients / Visits based on the queries. Patient addresses probably don't
change very often, so you could just have a separate form for those.

Patient Addresses data could populate (admittedly redundant) fields in
the Visits table, then would that be workable?

No. As in, don't store the addresses themselves in the visits table, just the
addrss ID. Then use unbound controls to *display* the address on your
visits subform if you need to, based on the address ID.

--
_________

Sean Bailey


"ll" wrote:

I am somewhat new to Access and am working on a form for a doctor that
will be used to gather patient data. There are three linked tables:
Patients, Visits, and PatientAddresses. The patient table's PatientID
key is related in a one to many relationship to the visits table's
v_PatientID foreign key. This has worked well in relating the patient
data to the visits data. The doctor then added the idea of keeping a
"historic" listing of patient addresses, keeping the address at the
time of the visit associated with that visit through a numeric
identifier. So, my thought was to add a PatientAddresses table with a
pa_PatientID foreign key. However, in setting up the query for the
form, once the form is run, even though all data displays ok, new data
cannot be input. The "Recordset Not Updateable" message appears.

Here is the SQL for the form query:
SELECT Patients.*, Visits.*, PatientAddresses.*
FROM (Patients LEFT JOIN Visits ON Patients.p_ID = Visits.v_PatientID)
LEFT JOIN PatientAddresses ON Patients.p_ID =
PatientAddresses.pa_PatientID;

I'm wondering if a separate form for Patient Addresses would help this
situation (if the problem is too many tables in one query)? If
Patient Addresses data could populate (admittedly redundant) fields in
the Visits table, then would that be workable?

Thanks for any help or resources in this,
Louis

Thanks. Should it matter what manner of subform is used in this
(whether embedded, dragged/dropped, etc), or do they 'behave' pretty
much the same across the board? I've started with a dragged/dropped
2nd form, which makes it a subform. Perhaps that would be a good
'display' form for the current patient address, but the 'add new
address' form could be an external form perhaps, linked, via button to
the main form..?


On Apr 3, 11:17 am, Beetle wrote:
I'm wondering if a separate form for Patient Addresses would help this
situation (if the problem is too many tables in one query)?

Yes. Multi-table queries like that are usually not updateable. Typically, you
would use separate queries for each table, then use a Main form / Sub form
for Patients / Visits based on the queries. Patient addresses probably don't
change very often, so you could just have a separate form for those.

Patient Addresses data could populate (admittedly redundant) fields in
the Visits table, then would that be workable?

No. As in, don't store the addresses themselves in the visits table, just the
addrss ID. Then use unbound controls to *display* the address on your
visits subform if you need to, based on the address ID.

--
_________

Sean Bailey

"ll" wrote:
I am somewhat new to Access and am working on a form for a doctor that
will be used to gather patient data. There are three linked tables:
Patients, Visits, and PatientAddresses. The patient table's PatientID
key is related in a one to many relationship to the visits table's
v_PatientID foreign key. This has worked well in relating the patient
data to the visits data. The doctor then added the idea of keeping a
"historic" listing of patient addresses, keeping the address at the
time of the visit associated with that visit through a numeric
identifier. So, my thought was to add a PatientAddresses table with a
pa_PatientID foreign key. However, in setting up the query for the
form, once the form is run, even though all data displays ok, new data
cannot be input. The "Recordset Not Updateable" message appears.

Here is the SQL for the form query:
SELECT Patients.*, Visits.*, PatientAddresses.*
FROM (Patients LEFT JOIN Visits ON Patients.p_ID = Visits.v_PatientID)
LEFT JOIN PatientAddresses ON Patients.p_ID =
PatientAddresses.pa_PatientID;

I'm wondering if a separate form for Patient Addresses would help this
situation (if the problem is too many tables in one query)? If
Patient Addresses data could populate (admittedly redundant) fields in
the Visits table, then would that be workable?

Thanks for any help or resources in this,
Louis