Hi,

I want to join (combine) two tables that have only identical column fields in common into one master table. There is no relation between the two tables. This master table I will use afterwards as basis for a excel pivot table. How can I do this?

Suppose I do not have identical column fields. Is it still possible to combine the tables and that in the new table the missings are filled with eg zero's.

Thx,
Agamemnon

You can use an Append query to do this.

Regards,
StepOne

Is this a one time deal or will you need to do this on a regular basis?

Ken

Actually

I import data for a certain category which has information on products for 52 weeks from excel into an access table. This table has as fields cat prod per1 per2 ...
I do the same for 10 other categories (in total more than 150.000 lines). and they all have the columns cat prod per1 per2 ...
Every 4 weeks new data arrives and the the oldest 4 periods are no longer available, new data is added and the database tables (one for every category) are filled with the new information

By the way KenHigg,
Happy Birthday

This table has as fields cat prod per1 per2

Do you actually import the data for all periods within one table, per category?

RV

If you mean by "join" that table 1 and table 2 are two separate lists that you want to work with as 1, you can "union" them. The names of the columns do not need to be identical but the column order and format do need to be identical. So if tbl1 is Name, Address, Phone, Salary, and tbl2 is FullName, BillAddress, Phone, CurrentSalary, then the union will be fine. If however, tbl2 is Name, Phone, Address, Salary, you would first need to make a query of tbl2 that reorders the columns. Then you could union tbl1 with the query of tbl2.

The basic syntax of the union is:

Select fld1, fld2, fld3
From tblA
Union Select fldA, fld2, fld3
From tblB;

Just to add to Pat's advice, if you have unmatched columns in your tables, you can either use a query to add colums based on a calculation, perhaps zero values as you suggest, or if appropriate miss out the unmatched columns from the query.

Hi,

Back from Holiday.
Here are some answers to your questions and more questionmarks from myself

1) to RV:
Actually I have 10 excel spreadsheets (workbooks) and I import every sheet into a seperate table. Afterwards I want to combine all the created tables into one "master table". I can't create a master table at once because the periodicity of every table differs.

2) To Pat Hartman
If I write code to union two tables. Do I have to define all the possible columnames for the periods. I ask this because some periodes like nov04 will disappair on my next update for cat 1, cat 2, cat 3, cat 4 and for the other categories updates only come within 6 months and within 2 years all the periods that are now in the database will be replaced.

3) To Neileg
As I'm quite a newbe, how would that calculation look like

Thx all of you for trying to help me out
Agamemnon

If I write code to union two tables. Do I have to define all the possible columnames for the periods. I ask this because some periodes like nov04 will disappair on my next update for cat 1, cat 2, cat 3, cat 4 and for the other categories updates only come within 6 months and within 2 years all the periods that are now in the database will be replaced.You need to have some way of matching up the data. If there is no logical match, Access can't pretend that there is one.
As I'm quite a newbe, how would that calculation look likeInset a blank column in the appropriate spot in the query grid. In the field name box you need a calculated field. It might look like
NewField:0
or
NewField:"No Data"Or whatever you need.