Please HELP! Stuck on task at work. Updating multiple tables from one form???

[JTQ911]

I was just assigned this database task couple weeeks ago, I know VERY LITTLE. I have all my tables and forms set up. My company builds custom cars....very few per year. I have dozens of tables for all the different componetns of the car....and each table has the same column "Unit" in it. I have a form that when a new car is began being built, the the first thing it does is ask the user to type in the Unit for the new car, but that only puts it in the one table i referenced when creating this form. I want/need this Unit information to be placed in every single table (20+) that has the column "Unit" in it. How can i do this, any information is greatly helped.....im getting so frusturated, i feel like i've learned a lot in the past 2 weeks since starting but i stil really know hardly anything.

 

[Rabbie]

It looks like your data(table) structure is not very good. Have you done a search on Normalization.

It might help if you use a query behind the form and then you could update the tables from the form.

 

[GaryPanic]

Ok

can you make it update 1 table using append qry Yes/No

if yes - then use this as the basis of your append qry you could do 20 or these qry and a little marco to run them all and a bit of coding behind the scenes - however from the sounds of this - the set up is slightly askew

 

[GaryPanic]

Rabbie - you beat me to it ..
g

 

[JTQ911]

i woudlnt even know how to do that, how to do i do that type of query....

Every single table in my database has a column called "SN_PN" In my form, the user types into a textbox and it adds the new unit number to "SN_PN", but only in table 597, because taht is the one i referenced while creating the form. Could somebody give me a step by step way to try so when the user types in the unit into "SN_PN" it puts this information in EVERY table with the column SN_PN

 

[GaryPanic]

now you in deep muck----

right get a copy of the d/base (copy......)
empty it all excpet 1 record (makes sure that theirs no personal data in it - set up something liek a mickey mouse account etc) then zip the file up and we will have a look - what version of access are you using

 

[JTQ911]

97 lol. gotta love my company

 

[GaryPanic]

ok - zip it up and post it

I am on 2000 so I figure it out then give instructions unless anyone else wants to chip in

 

[GaryPanic]

instructions (without see system)

ok
do this in a copy version of your data
open system - but get to the option where you can add tables/forms/queryes(qry )
righ now select the tab qry
new
add table this is an option along the top - or use the wizard to select the main table ( the table you start from the and where you add unit)
now you end up with a table with just the field names show in a screen - right now you need to select the primary key field - (hope you understand this) and also the field "Unit " SN_PN ( doggey field names) now add one of the tables - buy using hthe add table button
now on the menu bar on the screen in front of you should be some icons one of these is the qry icon and it has qry, append,delete etc, now change this to append it will change the screen ever so slight but it should automatically find the SN_PN field in your target table now run the qry

recap

qry will have the following fields

field
table
sort
show
critia
or

append qry

field
table
sort
append to
critia
or


once you have digested this the rest is so easy

now we do run foul of what happens if.. someone keeps shanging or selecting the unit - you would end up with mulitple entries -
lets deal wit that later - We will disable the option to change the unit type etc.... with a simple yees/no option .. but thats a last minute exercise and simple

 

[JTQ911]

sorry this took so long, had to remove a bunch of information per company standards, and then my computer froze....took 5 minutes to relog on... im gonna try posting it in my original post. thanks for you help, i appreciate it so much

 

[JTQ911]

okay im tryin this append query thing.....so u can only append to one table at a time.....so i may have to create a macro that runs the same query multiple times or a different query each time and each time it adds it to a different table?

 

[JTQ911]

and i feel like im gettin it, but im a little confused.....when im designing my append query...... if i type the data and it originally goes into table "General" under column "SN_PN". and i want it to also go into table "IOCA" under "SN_PN" as well...... what exactly do i want to have in those blank fields "field, sort, show, criteria, or" and since i only want to add one column to each table.........adding all those tables in teh beginning of designign my query wont help, cause since u can only append to one table at a time.....i have to have a differnet query to append to every different table. its so wordy, i apologize. this whole concept of relational database is just new and abstract to me. I give credit to anyone who can understand this and do thsi well. Thanks again