update a secondary table based on the master table

update a secondary table based on the master table

Post by dmlyZW » Wed, 13 May 2009 12:06:02


hi,

i have a master table that gets duplicated by an append query everytime we
have a new engine in the workshop.. this secondary table has extra fields
like engine number, serial number task cards etc. apart from all the fields
which are included in the master table..

if i were to add a few new records in the master file, how do i get the
secondary file to automatically add these new records to it.??


at the moment, i have 2 subforms on a main form. 1 subform is the master and
the second is the appended form. i add data in the master and then copy and
paste it to the secondary form.. is there a way i can avoid doing this????
please help
 
 
 

update a secondary table based on the master table

Post by John W. Vi » Wed, 13 May 2009 12:16:04

On Mon, 11 May 2009 20:06:02 -0700, viren < XXXX@XXXXX.COM >



You're not duplicating *data* in these two tables, I hope? The secondary table
should have nothing but the joining field (the Primary Key of both tables,
typically, for this kind of one to one relationship, if that's what this is).
You can base a Form on the master table and a Subform on the secondary table;
if you navigate to the desired engine on the mainform, then you can add data
to the secondary table fields and they'll be linked to that engine.

An Append query would NOT appear to be either necessary nor appropriate, nor
should any copy/paste be needed!
--

John W. Vinson [MVP]

 
 
 

update a secondary table based on the master table

Post by dmlyZW » Wed, 13 May 2009 12:37:01

hi john,
thanks for ur quick reply.


i work for a jet engine overhaul shop.

i am duplicating the data in the secondary table and adding an engine number
to it every time we have an engine for overhaul. i do this using an append
query.

this secondary table is then used to manage the parts as it goes through
inspection repair and made serviceable. so every record could have a
different inspection criteria.. it is not same for every engine. this is the
reason why i have a master template with just part numbers references to the
manual and no values asscociated with it. so i need to create new tables with
the same master data everytime an engine is in shop.

sometimes i have new numbers that need to be added to the master table.. .

i hope this makes sense. or i have structured the database the wrong way. i
hope not.
regards
viren
 
 
 

update a secondary table based on the master table

Post by John W. Vi » Wed, 13 May 2009 15:18:44

On Mon, 11 May 2009 20:37:01 -0700, viren < XXXX@XXXXX.COM >



Since you have not described how you structured your tables - rather just how
you use them - all I can say is "Do tell me more".

I would EXPECT that some of the Entities (real-life persons, things, or
events) of importance to your application would be Engines, Inspections,
Parts, and many to many junction tables between Engines and Inspections (each
engine will need many kinds of Inspections, and many engines will need a Rotor
Lubrication Inspection, say) and between Engines and Parts (a given engine
will have many, many Parts and each type of Part may be in many different
Engines). I see nothing there that would require storing data redundantly,
but... I don't know how to inspect jet engines, so I really don't know.

--

John W. Vinson [MVP]