Automatically updating multiple tables

[Rob]

I have a database that has several tables with the same columns. Can I set
it up so that if I add info to one of the tables it will automatically add
the same information to the other columns?

If there is a way to do it can someone please explain it to me?

Thanks in advance.

 

[tina]

why do you have several tables with the same fields ("columns" is an Excel
term)? and why are you saving the same data in multiple tables? both are
indications of an un-normalized database structure. can you expain what
you're doing, and why?

hth

 

[peregenem]

why do you have several tables with the same fields ("columns" is an Excel
term)?

Actually, when talking about a SQL or a DBMS table, the correct term is
'columns'. 'Fields' refers to resultsets (e.g. a recordset) and files.
Look at the ADO object model: the Recordset object has a Fields
collection
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjodbrec.asp),
whereas the Table object has a Columns collection
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/adobjtable.asp).
Therefore, in the context the OP was using, I think the correct term is
'columns'.

That said, the 'columns vs fields' debate is a regular occurrence so
lets not do it all again. Perhaps the best conclusion to arrive at is
that both terms are interchangeable: if the customer uses one term
then, rather than seek to correct them, be polite and use the same term
back. If you feel you must correct, be sure you *know* what is correct.

 

[Rob]

I am using this database to keep track of equipment issued to the men who
work for me.

The reason I have some of the same columns is because that is the way I set
the tables up. It seemed like the best way to do it at the time. This is
my first shot at creating a database. If there is a better way to do it, I
am willing to listen and learn

Each person is assigned a vehicle and certain things that go in that
vehicle.

I have a table for the vehicles and a table for each of those things
assigned

I hope I explained what I am doing. If necessary I can provide a copy of
the database or put it on my website. I know lots of people do not want to
chance downloading something from someone they don't know.

 

[Larry Daugherty]

Hi Bob,

If those fields/columns with the same names in multiple tables contain
the same data then you should re-think and re-work your data design.
Just as calculated results are not to be stored in a database,
duplicate data are not to be stored in a database.

In the design of your schema entities that play in your application
are represented in tables. Each table represents exactly one entity
type. Examples of entities are: person, employee, star, house, order,
event, etc.. Every entity of a given type belongs in the same table.
There is no real-world way that the specific attribute values of one
entity type map to an entity of a different type across the board.
The same is true of representing those real things in a database.

You may need to display and report data in many different ways but the
data exists only once in the tables.

You might have a look at "Rules for databases" at www.mvps.org/access
That site is a very valuable Access resource. You'll find lots of
useful things there.

HTH

 

[tina]

This is my first shot at creating a database.

you're right where every Access developer starts, Rob. for my "standard"
recommendations to newbies, see
http://home.att.net/~california.db/tips.html#aTip1

hth

 

[Fred Boer]

Dear tina:

So, is that your site? Is it new? I like the pumpkins!

Cheers!
Fred

 

[Rob]

Thanks I begin anew after checking that site.

Hi Bob,

If those fields/columns with the same names in multiple tables contain
the same data then you should re-think and re-work your data design.
Just as calculated results are not to be stored in a database,
duplicate data are not to be stored in a database.

In the design of your schema entities that play in your application
are represented in tables. Each table represents exactly one entity
type. Examples of entities are: person, employee, star, house, order,
event, etc.. Every entity of a given type belongs in the same table.
There is no real-world way that the specific attribute values of one
entity type map to an entity of a different type across the board.
The same is true of representing those real things in a database.

You may need to display and report data in many different ways but the
data exists only once in the tables.

You might have a look at "Rules for databases" at www.mvps.org/access
That site is a very valuable Access resource. You'll find lots of
useful things there.

HTH

 

[tina]

yes, that's my new, modest, and unassuming little site. <g>
it's pretty narrowly focused on newbie newbies; i mainly wanted a quick
reference for certain things i've found myself answering again and again in
the NGs - and since i'm better at explaining things with the "pointing and
waving arms" method, i thought the illustrated pdf instructions would come
in handy too. <bg>
i'm not doing anything to try to "get it out there" yet, because only about
half of the pdf files are available; i should get them done in the next
couple months.
as for the pumpkins - thanks, and could you guess that i built that page in
October?

 

[Fred Boer]

Well, good luck with it!I like the PDF files; they would be good for newbie
newbies. Are those files difficult to create?

Fred

 

[tina]

not difficult. i did the screen prints and the text in a Word doc, then used
CutePDF to print to .pdf file. the quality of the screen prints is degraded
in the conversion, but i think it's sufficient for "illustration of
instruction" purposes.

Well, good luck with it!

thanks, Fred!