How to do crosstab queries across databases?

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

I have databases each of which has around 20,000 records and each database
records the same data for individual months.

I want to do a crosstab query across the databases to extract distribution
of certain data. I know how to do a crosstab query over one database, but
not over several databases.

How should I do it? I'm very new to database and hopefully I can just
execute some commands to accomplish it without writing any vba code.

Thanks.

Herbert
 
Hello,

I have databases each of which has around 20,000 records and each database
records the same data for individual months.
Click to expand...

Are these multiple *DATABASES* - separate .mdb files, each a container
for multiple tables, forms, reports and other objects? or separate
*TABLES* within a database?

In either case - this is not good design. Storing data (a month) in a
tablename makes life difficult; storing data in a Database name is
much worse, and will lead to serious inefficiency. You would be much
better off having all the data in *ONE TABLE*, with an indexed date
field (or a month number field, if you are tracking whole months but
not dates).
I want to do a crosstab query across the databases to extract distribution
of certain data. I know how to do a crosstab query over one database, but
not over several databases.
Click to expand...

Assuming it's over several *tables* rather than databases, you need a
UNION query to "stitch together" your non-normalized tables into a
single normalized recordset. The UNION query requires that you go into
the SQL window, it can't be done in the query grid; type in something
like

SELECT "Jan" AS Month, thisfield, thatfield, theother FROM January
UNION ALL
SELECT "Feb" AS Month, thisfield, thatfield, theother FROM February
UNION ALL
<etc. etc.>

using, of course, your own table and field names.

Save this Query and base your Crosstab on the saved Query. It'll be
slow since Access must search twelve tables rather than one. You could
also base an Append query on the union query to get the suggested
normalized structure!
 
John,

Thanks for the very prompt response.

They are excel files actually linked to within the database.

There are around 30,000 records for each month, and such data are plugged
from different sources, and so it's like 5 files for each month, and we have
been collecting data for 4 years.

I don't know if it's good to keep data that way, it just seems to us that
the chance of corrupting a smaller file is smaller than for a gigantic
massive file. Is this true? It's been set up long ago by someone immemorial
anyway.

I actually don't quite understand what I must do. Thanks for the help and I
will try to decipher the 'cryptic' message you've written down.

Herbert
 
John,

Thanks for the very prompt response.

They are excel files actually linked to within the database.
Click to expand...

Well... it would have helped to say so. I was assuming that they were
Access tables! As such, my advice needs some revision; Excel
spreadsheets are limited to 64328 rows as I recall, so my suggestion
of putting all the data in one table won't work if that "table" isn't
a table, but a spreadsheet. Any chance of actually IMPORTING the data
into Access?
There are around 30,000 records for each month, and such data are plugged
from different sources, and so it's like 5 files for each month, and we have
been collecting data for 4 years.
Click to expand...

So there are up to 240 different spreadsheets scattered around!?
sheesh, you'll need a database just to keep track of them all!
I don't know if it's good to keep data that way, it just seems to us that
the chance of corrupting a smaller file is smaller than for a gigantic
massive file. Is this true? It's been set up long ago by someone immemorial
anyway.
Click to expand...

It is NOT true in my opinion. One (well backed up, well maintained)
Access database with a million or two row table is managable. Ensuring
that every one of some 240 Excel spreadsheets is secure, hasn't been
tweaked to add, remove, or swap columns, etc. would seem a lot more of
a challenge to me!
I actually don't quite understand what I must do. Thanks for the help and I
will try to decipher the 'cryptic' message you've written down.
Click to expand...

Take a look at UNION in the online help. It explains how to set up
such a query.
 
Back
Top