Help please.. how to update one column in many 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.

 

[Rich]

I suggest you do some reading here on normalis(z)ation before you go any further

 

[stopher]

You should not have a table for each component.

If I understand your problem correctly, you should have four tables

- one table that lists all the cars
- one table that lists all the components
- one table that lists what components are used on each car
- one table that represents a schedule of builds

table 1: Cars

CarID_____CarDescription
1__________Red Flame
2__________Blue Thunder


Table 2: Components

ComponentID_____ComponentDescription
1________________flashy wheel
2________________flashy wheel with gold trim
3________________plain wheel
4________________red wheel
5________________leather steering
6________________Wood steering


Table 3: CarComponent

CarID______ComponentID
1____________1
1____________5
2____________1
2____________6

Table 4: Build

CarID___Units
1________3
2________6

Your tables are then related using the ID's (in the relationship window).

Next you can create a Form/Subform with the Car as the main form record and the component as the subform record. This allows you to create a new car and list all the components for that car (assuming you have already set up some components).

You can then create another form to enter build quantities i.e. you enter a CarID and a quantity.

Once your data is entered you can create queries and reports that will list the number of components required etc (because the tables are related).

hope that makes sense.
Chris

 

[stopher]

JTQ911,
Rich and I are saying the same thing. Read up on normalisation then what I've said will hopefully make sense.
Chris