Combine Multiple Tables

[Rob]

Is it possible to create a table that gets all of its information from
multiple tables. All of the tables that I want to combine have the same
fields and field properties it's just that they have completely different
data. I need to keep that seperation but at the same time have a single
table that combines the others as well as keep itself updated as the others
are updated.

Thanks So Much,
Rob

 

[boblarson]

Rob -

That's called a QUERY and you can pull multiple tables together using the
query and you can use the query exactly as you can a table, with the added
benefit of that you don't need to create a separate table and you don't need
to make sure it is constantly updated as it always has the latest info when
you run or use it.

You can base a form or report off of a query instead of a table, so you are
covered there too.

So, my suggestion is use a query.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Larry Daugherty]

Change your design!

All of the data belongs in a single table. There should be a field in
the new/combined table that contains the identifier for each current
table: All records from tblRed have the value "Red" in that field.
All records from tblBlue have the value "Blue" in that field, etc.

HTH

 

[boblarson]

Good point Larry. I missed the fact that it was all the same fields and all.
Yes, if the same fields and the same type of data, you should consolidate to
a single table. You can use a make table query to create the initial big
table for one of the tables and then use an append query to append the
records from the other table(s) to that one. Then, get rid of the other
tables.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Rob]

So I should have one table with 90,000+ records (and next week it'll be
180,000+) instead of 9 managable 10K+ tables? Also, the seperate tables are
doled out to seperate peoples so that there isn't an issue with someone
saying "so-and-so changed it not me" and so forth.

 

[John W. Vinson]

Is it possible to create a table that gets all of its information from
multiple tables. All of the tables that I want to combine have the same
fields and field properties it's just that they have completely different
data. I need to keep that seperation but at the same time have a single
table that combines the others as well as keep itself updated as the others
are updated.

No. You do NOT need to have multiple identical tables, unless you have some
very strange security requirements.

You can accomplish your desired end by adding one more field to your table
structure, identifying which set of data the record is in. You can then create
Queries to display the desired set of data. The Query can be used as the
recordsource for a Form or Report, for updating, etc.; and you'll still have
all the data accessible for your combined reports.

If you insist on keeping the multiple tables, you will need to use a UNION
query to string them together. This will not be a table (you would *NOT* want
to store the same data redundantly!!!) and it will not be editable, but you
could at least generate reports and display data. See the online help for
UNION.

John W. Vinson [MVP]

 

[John W. Vinson]

So I should have one table with 90,000+ records (and next week it'll be
180,000+) instead of 9 managable 10K+ tables?

No. You'll have one managable (properly indexed) table instad of nine or ten
unmanagable tables. It's easier to herd one cow than it is to herd nine goats
(or so my livestock-owning friends tell me).

Also, the seperate tables are
doled out to seperate peoples so that there isn't an issue with someone
saying "so-and-so changed it not me" and so forth.

In a properly designed system, the user will never even SEE a table; they'll
be all linked to a shared server, and (if appropriate, as it seems in this
case) will interact with the data via a Form which displays only that subset
of the data for which they are responsible.

John W. Vinson [MVP]