Combining Tables

[Agamemnon]

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

 

[stepone]

You can use an Append query to do this.

Regards,
StepOne

 

[KenHigg]

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

Ken

 

[Agamemnon]

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

 

[Agamemnon]

By the way KenHigg,
Happy Birthday

 

[RV]

This table has as fields cat prod per1 per2

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

RV

 

[neileg]

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.

 

[Agamemnon]

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

 

[neileg]

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 like

Inset 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.