Too Many Tables in Form Query?

[ll]

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. Patients.PatientID (primary
key) is related to Visits.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 at a bit of a standstill with this and would appreciate any input
or resources which might facilitate these particular needs.

Thanks for any help or resources in this,
Louis

 

[Access_Saves_The_Day]

Hi Louis,
Your DataSource is not updateable because of the complex nature of your
query. If you see a LEFT JOIN in your query, it will not be updateable.

It looks like you are using the LEFT JOIN because otherwise you would not
see Patients that did not already have an Address or a Visit.

I would HIGHLY recommend using only a single table in the queries used for
forms.
In order to include the visits and addresses, you will want to build
SUB-FORMS for those tables. There should be some fairly decent Help files
and/or Tutorials for creating and using Sub-forms. Be sure to pay close
attention to the topic of the sub-forms LINK FIELDS.

Don't panic when the designer tells you that by embedding a subform in a
mainform, that you can no longer use Continuous Form mode. Access2007 has a
new form mode "Split Form" that should handle that problem for you.

Good Luck. Work Hard. Persevere. Access is a worthy opponent.
- Fritz

 

[Dirk Goldgar]

If you see a LEFT JOIN in your query, it will not be updateable.

This is not true. The majority of queries with a single outer join (LEFT
JOIN or RIGHT JOIN) are updatable. Many queries with two outer joins may or
may not be updatable, depending on other factors.

Louis should make sure that each of the three tables has a primary key.
There's a help topic, "When can I update data from a query?' in the help
files of most versions of Access, that treats the question of updatability
in some detail. The most common thing preventing updatabilty in three-table
queries is the existing of a many-to-one-to-many relationship; however, I
don't see that in what Louis posted.

I would agree with your suggestion that, for a form, displaying the related
data by way of subforms would be better, and would also probably eliminate
whatever factor is causing the non-updatability in this case. I only
challenge your statement that a left join makes a query non-updatable.

 

[Ken Sheridan]

Louis:

While the PatientAddresses table enables you to store multiple historic
addresses per patient, it does not in itself tell you what the address in
question was for each visit. For that you'd need to also relate the Visits
table to the PatientAddresses table. You haven't said what the primary key
of PatientAddresses is, but a logical candidate for this would be a composite
one of PatientID and AddressNumber (the last simply being a serial number per
patient from 1 upwards for each address occupied by a patient – this can
easily be set up to be inserted automatically when inserting a new address
record). The Visits table would then have a corresponding composite foreign
key of PatientID and AddressNumber.

I'd endorse what the others have said about not using a single form for data
entry but a patients form with visits and addresses subforms. BTW you can
have the two subforms in continuous form view with earlier versions of Access
than 2007. This is done by using correlated subforms. To do this you would
add a hidden text box control to the parent form referencing the
AddressNumber of the addresses subform, e.g.

=sfrAddresses.Form.AddressNumber

The LinkMasterFields property of the visits subform would then be a
combination of PatientID and this hidden control, e.g.

PatientID;txtAddressNumberHidden

Its LinkChildFields property would be the two fields in its own underlying
recordset the usual way:

PatientID;AddressNumber

As you navigate from address to address in the addresses subform the value
in the txtAddressNumberHidden control on the parent form would change and
consequently the visits shown in the visits subform would be for the
currently selected address only. When a new visit is added the current
PatientID and AddressNumber values would automatically be inserted into the
Visits table's new record by virtue of the linking mechanism.


There is a possible alternative model which would not require the
PatientAddresses table at all. It does mean, however, that any address
occupied by a patient, but at which they never received a visit, would
disappear from the database once the patient moves to a new address. Only
addresses where a visit was made would be retained on the 'historic' list.
If that's acceptable then the model is very simple.

The way it would work would be that both the Patients and Visits tables
would contain field for the address. This does not introduce redundancy as
might be thought at first sight, as the address data in Visits are attributes
of the Visits entity, not of the Patients entity (in the jargon they are said
to be functionally dependent on the key of Visits). You'll find a direct
analogy to this model in the sample Northwind database in fact. That has
UnitPrice columns in both the Products and OrderDetails tables, and inserts
the current unit price from Products into OrderDetails when an order detail
record is created. In your case the patients current address data from
patients would be inserted into Visits when a new visit record is created,
using code very similar to that in the AfterUpdate event procedure of the
ProductID control in the Order details subform in Northwind. In Northwind as
a product's unit price changes the relevant row in Products is updated, in
your case the relevant row in Patients would be updated when a patient
changes address.

Ken Sheridan
Stafford, England

 

[aaron_kempf]

I'm not sure that Access is 'Hipaa Compliant'
www.baynetug.org/uploads/HIPAA%20Compliance%20Application%20Guidelines.doc

Honestly-- I'm really against the idea of using MS Access anywhere in
the medical / billing / services industries.
MS Access just does _NOT_ include these functionalities


-------------------------------
Automatic Log Off
The system should require the user to re-login after a period of
inactivity. This can be handled as a standard operating procedure
(patient data should not remain on an unattended computer screen) and
as an application function. Windows based clients can use a timer to
require the user to re-verify their password.
--------------------------------------
Require Strong Passwords
It is recommended that a strong password policy be used to access the
application. Passwords should be at least eight characters long
containing at least one uppercase letter, one lowercase letter one
number and one symbol. For strong password guidelines see
http://www.microsoft.com/athome/security/privacy/password.mspx.
--------------------------------------
Password Change Policy
Periodically users should be forced to change their password either by
the application or by a standard operating procedure.
--------------------------------------

SQL Server supports this functionality-- right out of the box.

HTH

-Aaron

 

[ll]

Louis:

While the PatientAddresses table enables you to store multiple historic
addresses per patient, it does not in itself tell you what the address in
question was for each visit. For that you'd need to also relate the Visits
table to the PatientAddresses table. You haven't said what the primary key
of PatientAddresses is, but a logical candidate for this would be a composite
one of PatientID and AddressNumber (the last simply being a serial number per
patient from 1 upwards for each address occupied by a patient – this can
easily be set up to be inserted automatically when inserting a new address
record). The Visits table would then have a corresponding composite foreign
key of PatientID and AddressNumber.

I'd endorse what the others have said about not using a single form for data
entry but a patients form with visits and addresses subforms. BTW you can
have the two subforms in continuous form view with earlier versions of Access
than 2007. This is done by using correlated subforms. To do this you would
add a hidden text box control to the parent form referencing the
AddressNumber of the addresses subform, e.g.

=sfrAddresses.Form.AddressNumber

The LinkMasterFields property of the visits subform would then be a
combination of PatientID and this hidden control, e.g.

PatientID;txtAddressNumberHidden

Its LinkChildFields property would be the two fields in its own underlying
recordset the usual way:

PatientID;AddressNumber

As you navigate from address to address in the addresses subform the value
in the txtAddressNumberHidden control on the parent form would change and
consequently the visits shown in the visits subform would be for the
currently selected address only. When a new visit is added the current
PatientID and AddressNumber values would automatically be inserted into the
Visits table's new record by virtue of the linking mechanism.

There is a possible alternative model which would not require the
PatientAddresses table at all. It does mean, however, that any address
occupied by a patient, but at which they never received a visit, would
disappear from the database once the patient moves to a new address. Only
addresses where a visit was made would be retained on the 'historic' list.
If that's acceptable then the model is very simple.

The way it would work would be that both the Patients and Visits tables
would contain field for the address. This does not introduce redundancy as
might be thought at first sight, as the address data in Visits are attributes
of the Visits entity, not of the Patients entity (in the jargon they are said
to be functionally dependent on the key of Visits). You'll find a direct
analogy to this model in the sample Northwind database in fact. That has
UnitPrice columns in both the Products and OrderDetails tables, and inserts
the current unit price from Products into OrderDetails when an order detail
record is created. In your case the patients current address data from
patients would be inserted into Visits when a new visit record is created,
using code very similar to that in the AfterUpdate event procedure of the
ProductID control in the Order details subform in Northwind. In Northwindas
a product's unit price changes the relevant row in Products is updated, in
your case the relevant row in Patients would be updated when a patient
changes address.

Ken Sheridan
Stafford, England

Ken,
Thanks again for your reply and help in this.
Would this also work with the Visits form as the main form and
Patients and Addresses as subforms, or would it be best to have the
Patients form as the main one?
Also, would the AddressID (primary key) mentioned in your first
paragraph be programmatically constructed as a composite of the
PatientID and AddressNumber? Would this be achived perhaps through
VBA in a module?

Thanks again,
Louis