Joing many tables into one

[Guest]

I have 7 tables which have the same fields (18 of them) the only difference
each table is a separete month. and each table has well over 300,000 records.
I want to join all the tables into one so I can sort and run reports faster.
Is there a way to do this? I would use Excel but of course it has a limit on
the number of records you can use.

Does anyone know how I can combine these tables into one workable table. If
possible I will have over 2.4 million records in one when finished. Or am I
just dreaming on this ?

 

[Rick B]

You don't need to combine the tables, just pull them all into one query
using a UNION query and then run your reports off that query.

 

[John Vinson]

I have 7 tables which have the same fields (18 of them) the only difference
each table is a separete month. and each table has well over 300,000 records.
I want to join all the tables into one so I can sort and run reports faster.
Is there a way to do this? I would use Excel but of course it has a limit on
the number of records you can use.

Does anyone know how I can combine these tables into one workable table. If
possible I will have over 2.4 million records in one when finished. Or am I
just dreaming on this ?

Rick's UNION query is certainly one option (see UNION in the online
help if the term isn't familiar). Alternatively, you could create a
single master table (by copying one of your monthly tables, design
mode only, into a new table) and run seven Append queries to populate
it.

If a single .mdb file can hold your seven tables, then it can
certainly hold the same data in a single table. You'll need to pay
careful attention to indexing and proper query design (no IIF or
domain functions in the query if you can help it!), and you'll need to
regularly compact the database; but I know of Access tables ten times
larger in routine productive use.

John W. Vinson[MVP]

 

[Van T. Dinh]

A Union Query (multiple unions) would be work-around. Check Access Help on
the Union Queries.

OTOH, they obviously represent the same class of items (i.e. same entity in
the database structure) and therefore, they all should be stored in the same
Table with a Field for the date/period rather than 18 Tables. If you have
Table names like:

tblJan2004
tblFeb2004
.....

, you are storing data in the names and JET (the database engine) won't be
able to process the data stored in the names.