Error: The query cannot be completed

[jhrBanker]

My Access2007 db contains 3 tables with a total of just under 5 million records
TBL1: 2,631,933
TBL2: 1,168,989
TBL3: 1,159,542
TOTAL: 4,960,464
All tables are identical, with 10 fields each. The db size is 862MB. I'm
running WinXPpro with 20gb free space.

When I try to run a Union query to combine the 3 tables in a recordset, I
receive the following error:
"The query cannot be completed. Either the size of the query result is
larger than the maximum size of a database (2GB), or there is not enough
temporary storage space on the disk to store the query result."

I created a new empty db and linked the 3 tables to it, and am still unable
to run a Union query (same error).

Any suggestions?

 

[Jerry Whittle]

Try running it as a UNION ALL instead of just a UNION. A UNION ALL doesn't
the time, effort, and disk space to eliminate duplicates.

 

[jhrBanker]

Thanks Jerry. That did it. Muchly appreciated.

Try running it as a UNION ALL instead of just a UNION. A UNION ALL doesn't
the time, effort, and disk space to eliminate duplicates.

 

[De Jager]

My Access2007 db contains 3 tables with a total of just under 5 million
records
TBL1: 2,631,933
TBL2: 1,168,989
TBL3: 1,159,542
TOTAL: 4,960,464
All tables are identical, with 10 fields each. The db size is 862MB. I'm
running WinXPpro with 20gb free space.

When I try to run a Union query to combine the 3 tables in a recordset, I
receive the following error:
"The query cannot be completed. Either the size of the query result is
larger than the maximum size of a database (2GB), or there is not enough
temporary storage space on the disk to store the query result."

I created a new empty db and linked the 3 tables to it, and am still
unable
to run a Union query (same error).

Any suggestions?