How Do I Join Multiple Tables into a Single Table?

[mnwyche]

I have multiple Excel worksheets that I used to generate multiple
tables in Microsoft Access 2000, approximately 45. Each table has the
same fields, e.g. Level_1, Level_2, Level_3, Type, Group and a primary
key. The tables are differentiated by business area.

I would like to take these 45 tables and unify them into a single
(global) table.

So, for example... the Level_1 field should have Level 1 records from
all 45 tables, the same for Level_2, Level_3, Type and Group.

The only thing I have been successful at doing is getting this
information merged into a single spreadsheet, but displaying as...

BusinessUnit1.Level_1, BusinessUnit1.Level_2, BusinessUnit1Level_3...
BusinessUnit3.Level_1, BusinessUnit3.Level_2, BusinessUnit3Level_3...

Is there any way to unify these different tables according to field
name, since they all share the same field names and display vertically?

e.g. Level_1

BusinessUnit1 Data
BusinessUnit2 Data
BusinessUnit45 Data

the same example would apply across columns. Any information would be
very much appreciated.

Thank you.

 

[Vincent Johns]

I have multiple Excel worksheets that I used to generate multiple
tables in Microsoft Access 2000, approximately 45. Each table has the
same fields, e.g. Level_1, Level_2, Level_3, Type, Group and a primary
key. The tables are differentiated by business area.

I would like to take these 45 tables and unify them into a single
(global) table.

So, for example... the Level_1 field should have Level 1 records from
all 45 tables, the same for Level_2, Level_3, Type and Group.

The only thing I have been successful at doing is getting this
information merged into a single spreadsheet, but displaying as...

BusinessUnit1.Level_1, BusinessUnit1.Level_2, BusinessUnit1Level_3...
BusinessUnit3.Level_1, BusinessUnit3.Level_2, BusinessUnit3Level_3...

Is there any way to unify these different tables according to field
name, since they all share the same field names and display vertically?

e.g. Level_1

BusinessUnit1 Data
BusinessUnit2 Data
BusinessUnit45 Data

the same example would apply across columns. Any information would be
very much appreciated.

Thank you.

It looks as if you've done most of the work you need (combining the
records into one big table). In Access, you will probably need one
additional field, [BusinessUnit], which will have values of 1, 2, 3, ...
(or "Unit1", "Unit2", "Unit3", ...), according to which worksheet a
record originally came from. Otherwise, a record in your Access Table
will look a lot like a record in one of the Excel tables.

Well, you might also want to add a second new field, [BusinessDataID],
to serve as a primary key; you could declare this as an Autonumber type.
But you can, if you wish, wait to add that until after you have imported
your Excel data.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[mnwyche]

"Well, you might also want to add a second new field, [BusinessDataID],
to serve as a primary key; you could declare this as an Autonumber
type. But you can, if you wish, wait to add that until after you have
imported your Excel data."


Thanks for the information. The data has already been imported from
Excel, using all of the Field (column) names that I specified in my
first post. Therefore, an autonumber has already been established and
that field presently holds the primary key. I will try your suggestion
and change this field to BusinessDataID..

If I understand you correctly, I should specify a field to create an ID
for each BusinessUnit. I'll give that a try and see how things go.
Hopefully I'll have some luck.

Thanks.

 

[Vincent Johns]

"Well, you might also want to add a second new field, [BusinessDataID],
to serve as a primary key; you could declare this as an Autonumber
type. But you can, if you wish, wait to add that until after you have
imported your Excel data."

Thanks for the information. The data has already been imported from
Excel, using all of the Field (column) names that I specified in my
first post. Therefore, an autonumber has already been established and
that field presently holds the primary key. I will try your suggestion
and change this field to BusinessDataID..

It doesn't matter too much what you call it, as long as you can remember
what it is. I use names like this to help me remember that it's a key
value, and what Table it belongs to, but some other system might work
better for you.

If I understand you correctly, I should specify a field to create an ID
for each BusinessUnit. I'll give that a try and see how things go.
Hopefully I'll have some luck.

Thanks.

You'd use that [BusinessUnit] value in cases where you need to identify
where a datum came from. For example, you might use it in a calculated
expression in a Query.

Since you've already imported the records, you may have lost track of
where they came from. If so, you can either reconstruct the information
manually by inspecting the records and generating the [BusinessUnit]
values fro them (ugh), or possibly you might find that you'd save time
by re-importing everything, same as before, but this time including the
[BusinessUnit] number with each set of records. I've had to do that a
couple of times (and as a result I've become kind of careful about not
losing information like that when I import stuff).

-- Vincent Johns <[email protected]>
Please feel free to quote everything I say here.