Automatically Updating Multiple Tables

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I am creating a database with multiple tables. Each of the tables will have
some of the same columns. Those columns are titled

computer number
car number
rank
last name
first name
middle name

What I would like to know how to do (if it can even be done) is to be able
to add the information to one of the tables and have it automatically added
to the rest of the tables.

Anyone know how to do this or even if it can be done?

Thanks.
 
I am creating a database with multiple tables. Each of the tables will have
some of the same columns. Those columns are titled

computer number
car number
rank
last name
first name
middle name

What I would like to know how to do (if it can even be done) is to be able
to add the information to one of the tables and have it automatically added
to the rest of the tables.

Anyone know how to do this or even if it can be done?
Click to expand...

It can be done, with some difficulty.

It SHOULD NOT BE DONE, however.

Storing information redundantly is NEVER a good idea. Any relational
database follows the "Grandmother's Pantry Principle" - "a place - ONE
place! - for everything, everything in its place".

Information about a Person should be stored in a People table, with a
PersonID, and biographical data (names, perhaps rank); information
about computers in a Computers table, with a Computer Number as its
primary key, and a description of the computer, etc.

When you need this information in conjunction with data in another
table, that other table should have ONLY a link (a "foreign key") to
the Primary Key of the People table, the Computer table, or whatever;
it is neither necessary nor appropriate to copy the data from these
tables over into another table.

Instead, you'll create Queries joining the tables; you can then pick
the name information from one table, computer information from
another, yet other information from a third.

If you have some good reason to want to do this, please post back
explaining why; as I say, can be done, but it's almost surely the
wrong approach.

John W. Vinson[MVP]
 
Not a really good reason. That is just how I started building the database.
I am new to this which I guess is obvious. Actually the computer number is
the employee number for the department.

I really appreciate the help.
 
So none of the tables should have any common information?
Click to expand...

Each related table should have ONE common field - the "foreign key"
field linked to the Primary Key of the "one" side table.

For example, if you have a table of People with the primary key being
the [Computer No] field, then every table containing information
*related* to people should contain a field of the same datatype and
size as Computer No. Typically (but not essentially) you'ld name it
[Computer No] as well; some folks use other naming conventions, such
as fkComputer_No (meaning "foreign key to Computer_No").

Check out the Relational Databases 101 link at

http://home.bendbroadband.com/conradsystems/accessjunkie.html

for some pointers to how relational databases can make your life
easier (once you climb that rather steep and rocky learning curve!)

John W. Vinson[MVP]
 
Back
Top