How to extract data from multiple Access databases to create a Pivot table

  • Thread starter Thread starter Android
  • Start date Start date
A

Android

Hi,

An application system we have creates one MS-Access MDB file for each month,
which captures data by the hour. At the end of each month, the system
creates a file for the next month, which is then automatically updated each
hour during that month.

I have created a PivotTable & PivotChart which produces a table and chart
against one of these databases. Another sheet in the same workbook does the
same for the second month, etc.

I would like to produce one Pivot Table & Chart which captures data from all
of these.

My two related questions are:
1) Is it possible to create a Pivot table which reads more than one MDB
file?.

2) I also looked into creating a Pivot Table which would read the other
Pivot tables in the worksheet. However the option to use "Another PivotTable
or PivotChart" is greyed out. Does anyone know how to enable it?

Regards..
 
You're probably going to give me a 'good' reason for using the
approach you are using, but I think your database strategy is wrong. I
would expect one table in one .mdn file, with a Date (or perhaps
Month) column to key the data by month. Then the solution to your
current problem is obvious.
 
Hi,

My 'good' reason is that I don't have control over the .mdb files. These
are produced by the Nortel's BCM system which tracks calls to the Call
Centre.

The first column of the table I am using from this database does have the
date (one field which captures Year, Month, Day, Hour....).

I could not find a way in the Pivot tables to append one table to the other.
I think that would solve it.

Regards..
 
Well, if you *must* work with the system... I take it you are using MS
Query to get the data, so there will be an underlying SQL SELECT
statement. You need to change the SQL to append multiple SELECT
statements using the UNION keyword e.g. I just tested this one:

SELECT T1.RefID
FROM `C:\Tempo\New_Jet_DB_1`.PersonalDetails T1
UNION ALL
SELECT T2.RefID
FROM `C:\Tempo\New_Jet_DB_1`.PersonalDetails T2
ORDER BY 1
 
Back
Top