Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Hi All
Happy New Year!! :) We have huge huge data, and evry month number of records just keeps increasing... currently we have aprrox 350,000 lines of records and by year end it would be approx 1000,000 lines of records. I am using Pivot Table to produce different reports based on this data in Excel 2003 and have below queries, Note: We use MS Access DB to store this data, that too we use link table to view the data. 1. I understand Pivot Table uses its own memory to store the copy of the data, but still how many number of records pivot table can store? I am using 2 MB RAM and disk space is not an issue. 2. As i mentioned earlier we use MS Access to store our data in virtual form. (Link Table) I can't really copy paste such a husge data on local drive in MS Access Project, but yes fortunately I could connect through Pivot Table. It took me aprrox 4 hours to download 350,000 lines of record in Pivot Table? Is there any way to reduce this timing? 3. I am planning to distribute this data in multiple tables in MS Access by writtign few queries so that I can have a local copy of all data. I understand we can use multiple ranges in Excel for Pivot table, but CAN we connect Pivot Table to Multiple MS Access Tables? Note: All tables will contain same number of fields, Tables with same number of columns but different set of data. If not is there any way to handle such a huge data?? 4. I also would like to add few fields to my MS Access tables, which I really cant do in View form but yes I can add in Local MS Access DB. To avoid this can I add extra field in Pivot Table? Here I am not talking about calculated functions I need extra field altogether in Pivot table. e.g Under Country Field I have got multiple countries like USA, UK, India, China etc Now I want to add one flag field to say 'UK-Flag' to club UK and all other countries by "Y" and "N" I am really looking forward your help on this. Your response would be highly appreciated. Thanks!! Milind Keer |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I connect to access 2007 data from excel? | Excel Discussion (Misc queries) | |||
Excel Pivot Table connected to an Access Database Data Source | Excel Discussion (Misc queries) | |||
Open excel pivot source data in Access? | Excel Discussion (Misc queries) | |||
Data Entry Form Connect With Access | Excel Discussion (Misc queries) | |||
Access Create Excel Pivot Table Data Source | Excel Discussion (Misc queries) |