Updating query

[Ian]

I have a db of hospital patients which consists of a main
table with patient operation data and then a number of
detail tables that hold data relative to operations
undergone and various techniques relating to each
procedure. The tables are all fully keyed and related with
referential integrity on the primary keys being enforced.
The relationships are one-to-one and one-to-many as
required. However, one item of data in the main table (not
a keyed field) needs changing in all the linked tables
where there that field exists.

I tried an update query with all the tables in the query
but it failed when a particular table didn't have a record
for that patient (which can occur with the nature of the
procedure). However, I can't simply leave this table out
of the query as it may be needed fot the next patient.

What I'm getting at is how to construct a query that will
update a field in a linked table from one table where that
field exists for that patient. I really want a generic
query that I write once and can be used repaetedly without
alteration. It would seem possible as Cascading Updates
work in referential integrity when related to primary
index keys. I really don't want to have to include this
field I'm trying to update in the primary as it would
upset all the relationships I have created.

Thanks very much for any ideas,

Ian.

 

[PC Datasheet]

Create an update query that includes all the tables that hold this field.
Pull that field from each table into the query grid. In the first column,
enter what you need to update the field to where it says "Update To". In all
the other columns where it says "Update To", enter TableXYZ.NameOfField
where this value comes from the first column.

 

[Ian]

Thanks for your speedy reply, does this mean then that I
have to change the query dependant upon whether their is a
related record in the one-to-many tables that requires
updating?

Thanks,
Ian.

 

[Guest]

I know this probably doesn't help much, but if at all possible, I would
correct your data structure by removing this field from all but the main
table. Your structure is not truly relational if you are repeating data in
multiple tables, all keyed by a patientID or procedureID. You can always get
at this data by creating a query that joins the maintable to the subordinate
tables if you need this piece of information.

Dale