Most efficient way to make changes in one table appear in another

[AccipiterQ]

Ok, so I'm making a database for this place, about 40 tables in it, each table contains an organization name,address, phone #, contact name, etc. There is also a table that is going to be a master list. The master list is a combination of all 40 of these tables. Now what would you guys use to make it so that when I make changes in any of the tables, the changes appear in the master list. Also it would be helpful if I make changes in the master list they appear back in the tables from which that particular entry in the master list came from. I see a bunch of different ways listed in help, so I'm confused as to which would be the simplest.

 

[PHV]

I strongly suggest you read this:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

AccipterQ,
What PHV is saying, is that your description sounds like a very poor database design. That reference is a very good starter. Normally, each table contains all the fields of like information. So in your case from what you described you should have only one table not 40. Maybe something like

tblEmployees
longEmployeeID
strFirstName
strLastName
strContactPhone
strOrganization
strGender
strRace
intAge

blah, blah

Now you use reports to filter, sort, or group this information. So you could have a "Marketing Division" report where you filter on strOrganization = "Marketing Division" and only show people in the Marketing Divion, sorted by last name.

Good Luck

 

[AccipiterQ]

thanks guys...I'll see if I can persuade the people I'm working for to consolidate it into one master list

 

[AccipiterQ]

now that I look at it, they really don't need multiple tables, I'm going to persuade them to just use one larger table, and I'll put in a field indicating which list the entry came from. Thanks guys...that really simplifies things