Multiple Table Query

[Guest]

I receive a weekly csv report containing "package-level detail" for FedEx
shipments. The reports contain 36 columns and, on average, 50,000 rows. In
the past I would import each report into one table in Access. The problem I
would run into is that the table would become too large and the database
would start giving me errors. I would like to link the files to one database
and have a query that would search all the tables for specific critera and
return the results in one table. Whenever I try to set up the query, it
always includes all the columns from each queried table in the new table. I
thought I might try a cross-tab query, but I don't want to calculate the
fields. What can I do? Or do I need to do this in Sequel?

 

[John Vinson]

I receive a weekly csv report containing "package-level detail" for FedEx
shipments. The reports contain 36 columns and, on average, 50,000 rows. In
the past I would import each report into one table in Access. The problem I
would run into is that the table would become too large and the database
would start giving me errors. I would like to link the files to one database
and have a query that would search all the tables for specific critera and
return the results in one table. Whenever I try to set up the query, it
always includes all the columns from each queried table in the new table. I
thought I might try a cross-tab query, but I don't want to calculate the
fields. What can I do? Or do I need to do this in Sequel?

It sounds like you should store the data all in one table, not one
table per file. You're limited to 2GByte in any single .mdb file, so
you'll hit the database-too-big limitation either way - in one table
or in multiple tables.

If you do want to keep the data externally in text files, you can use
a UNION query to string them together "end to end". Crosstab queries
will NOT help, and neither will a join query; see UNION in the offline
help.

Note that linking to external text files, and running UNION queries,
are both going to affect performance very negatively. However,
2,600,000 rows (a year's worth of files) is still within Access'
capabilities, unless each row contains an average of 800 odd bytes
(which would make this table push 2 GByte); are you certain that you
can't just run Append queries into one table, with regular compaction?


John W. Vinson[MVP]