How to join 5 tables with the same layout - use the query?

[Efendak]

Hello all,

I have 5 tables with lot of data in each (over 100K rows). All 5 tables has the same fields. It is data for 5 different months. I would like to have all these data in 1 table. Could you please advice how to join them? Should I use some type of query?
I am newie at Access...

Thank you

Regards,



Marian

[lfpm062010]

Did you try use "UNION" or "UNION ALL"?
UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

[Efendak]

Thank you, it works when joining 2 tables. I used this code:
SELECT July.*
FROM July
UNION ALL
SELECT August.*
FROM August;

But what is I need to join 5 different tables? How to write the code please?

Thank you

Marian

[pbaldy]

You just keep going:

SELECT July.*
FROM July
UNION ALL
SELECT August.*
FROM August
UNION ALL
SELECT ...

[Efendak]

Hello Phaldy,

that is exactly what I did, even before sending my 2nd question. But after using:

SELECT July.*
FROM July
UNION ALL
SELECT August.*
FROM August
UNION ALL
SELECT September.*
FROM September;


Access says: "Too many fields defined". What I do wrong please?

[pbaldy]

Are you sure each table contains the exact same fields? You might try specifying, making sure to get them in the same order:

SELECT Field1, Field2, Field3
FROM July
UNION ALL
SELECT Field1, Field2, Field3
FROM August
UNION ALL
SELECT Field1, Field2, Field3
FROM September;

[Efendak]

The structure of all tables is the same. But I found this: each my table includes 124 fields. This is probably too much for Access query, that is why it says "Too many fields defined". I tried to run the query with 85 fields only and it works. So probably some restriction within Access.

Thank you guys for your help.

Regards,

Marian

[pbaldy]

Oy, that's a lot of fields. Glad you found a solution. Are you normalizing the data into 1 table I hope?

[Efendak]

I am joining all months into 1 table to have option run queries over all months at one bout. Excel has difficulties to store so much data, so now I am learning Access. If I was an expert at Access, all my work would be done in a while and I could play chess instead :-)

[pbaldy]

You will be happier with the data in one table. Welcome to the site by the way!

[June7]

Run 5 INSERT SELECT queries to get the data combined into a single table.

[Efendak]

Finaly I used INSERT INTO Statement and that joined all 5 tables into 1. Thank you guys.

Marian