Cascade update tables through form entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a form off of a query that is linked to a
number of tables. I want to use the form for data entry.
Is there a way to type my unique identifier into just one
window and have it update to all my tables?
 
I created a form off of a query that is linked to a
number of tables. I want to use the form for data entry.
Is there a way to type my unique identifier into just one
window and have it update to all my tables?
Click to expand...

With some difficulty, yes: BUT THIS WOULD BE A VERY BAD IDEA.

Creating empty "placeholder" records, with just a foreign key, is
neither necessary nor wise. They should be created by using a Subform
bound to each related table, using the ID as the master/child link
field. Basing a single Form on a multitable query is rarely a good
approach.

If you're using the same ID as the Primary Key of all your tables you
probably have an incorrect table design; one to one relationships
(which this implies) is *very* rare.

What are your tables? How are they related?
 
John,
My tables are of hospital samples being tested in various
tests. Therefore I have one main table of patient info,
patient number as unique identifier and a separate table
for each test. Since the same patient can appear more
than once for each test you are correct the relationship
is one:many. I thought I could make a composite key
using the autonumber. To bring all the info together in
a form I have to do it through a query. My big problem
is that the people entering data aren't going to go to
each table and fill in the patient number. I need
someway to enter the identifying data into the form once
and have it cascade to all the tables. Can you help?
-----Original Message-----
Click to expand...
 
My tables are of hospital samples being tested in various
tests. Therefore I have one main table of patient info,
patient number as unique identifier and a separate table
for each test. Since the same patient can appear more
than once for each test you are correct the relationship
is one:many. I thought I could make a composite key
using the autonumber. To bring all the info together in
a form I have to do it through a query. My big problem
is that the people entering data aren't going to go to
each table and fill in the patient number. I need
someway to enter the identifying data into the form once
and have it cascade to all the tables. Can you help?
Click to expand...

So... is EVERY PATIENT going to get EVERY TEST? Surely not. You would
not *WANT* to automatically fill in the patientID in every one of the
test fields!

It sounds like what you in fact have is a Many to Many relationship:
each patient will have one or more tests and each test will be given
to one or more patients. Is this correct? If so, having a separate
table for each test is questionable unless the tables all have
drastically different structure. Normally one would model this with
three tables: Patients; Tests, with a TestID, description, and other
fields pertaining to the nature of the test itself; and a "resolver"
table Results, with fields for the PatientID, the TestID, and enough
fields to enter the results for any test. I agree that this may not
suffice if the data from different tests has radically different
structure.

Whether you have one Results table or several, though, you would NOT
fill in the PatientID in every result. Instead you could use a Subform
(or, for the multitable approach, multiple subforms, displayed on
different tab pages on your form). The Subform has a Master Link
Field/Child Link FIeld, PatientID in your case; when you enter a
result into the table the PatientID will fill in automatically *just
for that test*, without the need to enter records which will never be
filled out.
 
Back
Top