If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to extract data from multiple Access databases to create a Pivot table
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 a 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.. |
#2
|
|||
|
|||
How to extract data from multiple Access databases to create a Pivot table
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. -- "Android" wrote in message ... 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 a 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.. |
#3
|
|||
|
|||
How to extract data from multiple Access databases to create a Pivot table
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.. "onedaywhen" wrote in message om... 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. -- "Android" wrote in message ... 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 a 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.. |
#4
|
|||
|
|||
How to extract data from multiple Access databases to create a Pivot table
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 -- "Android" wrote in message ... 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.. |
#5
|
|||
|
|||
How to extract data from multiple Access databases to create a Pivot table
That was great. Thank you. Works well.
Android... "onedaywhen" wrote in message om... 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 -- "Android" wrote in message ... 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.. |
Thread Tools | |
Display Modes | |
|
|