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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Answers interspersed:
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. Max number of records depends on the size of each record (number and type of fields) and also the maximum RAM useable by a pivot table depends on the version of Excel (later versions generally have larger limits). If you really mean 2 MB of RAM thats way too small. If you mean 2GB of RAM thats OK. I have successfully loaded over a million records into a pivot table. 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? Connect directly using a fast link, or copy the data to your local disk. 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?? Maybe you could use a UNION query. 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" You may be able to add a calculated Pivot table field, or else use Pivot Table Grouping. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Hi Charles
Thanks for your reply. Sorry!! RAM size was typo mistake, I am using 2GB RAM :):) Do you think 2GB RAM would be enough to download 1000,000 records which has approx 150 columns? When I downloaded 350000 lines (1/3rd of expected data) in Pivot Table, it took me 4 hours and size of the file is 350 MB? Just wondering whether Excel could handle 1000,000 lines of record with 150 columns down the line (with same configuration) What was the size of your excel file when you downloaded millions lines of record? And what was PC configuration? I can not really copy the data on my local drive in MS Access because of size, I tried writting a query and simple copy paste but got an error to many records. Currently I am using virtual view (Link Tabble) to view the data and that data i could download in Pivot Table. How to copy such a huge data on a local drive in MS Access?? Big Question?? "UNION query" - what is it? I am not DB guy and need to explore it? I tried calculated pivot table field but i guess that we can use only for DATA Items and want to use new field in Row area or Page area? How to do that?? Again thanks for your reply and looking forward more comments... Thanks!! Milind "Charles Williams" wrote: Answers interspersed: 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. Max number of records depends on the size of each record (number and type of fields) and also the maximum RAM useable by a pivot table depends on the version of Excel (later versions generally have larger limits). If you really mean 2 MB of RAM thats way too small. If you mean 2GB of RAM thats OK. I have successfully loaded over a million records into a pivot table. 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? Connect directly using a fast link, or copy the data to your local disk. 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?? Maybe you could use a UNION query. 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" You may be able to add a calculated Pivot table field, or else use Pivot Table Grouping. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Hi Milind,
Do you think 2GB RAM would be enough to download 1000,000 records which has approx 150 columns? Maybe, if nearly all the columns are numbers and you have Excel 2003 or Excel 2007, but the only way to find out is to try it out. I can not really copy the data on my local drive in MS Access because of size I don't understand the problem, its only a million rows and you don't need the complete database: Use an Access MakeTable query to create a table that has only the columns you want, then Export the table to a new MDB, then copy the MDB to your local disk. "UNION query" - what is it? I am not DB guy and need to explore it? See ACCESS help on UNION QUERY I tried calculated pivot table field but i guess that we can use only for DATA Items and want to use new field in Row area or Page area? How to do that?? I would create a query in your access DB that gives you the data columns plus the column you want, then read that query into the pivot table. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Hey Charles
Thanks again for the prompt reply. In my Access I am not downloading complete DB, What I am doing here is I am using ODBC (& data source) Connection to link the database. By getting doing this I can see all the data but I can't really add new fields to this table because this is just a view. Now to get a local copy of Data i have already tried Make Table query but after some time getiing error "To many records" :( That is why I said I can't really copy data on my local copy? And because of this I can't add new field and can't get that new field in Pivot table.... Do you think is there any other way handling this issue?? I m thinking of using SQL client instead of MS Access, will that solve the problem?? Please advise. Thanks again!! Milind Keer |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Why can't you create a query on the Access server that produces the old and
new columns you want and then use the ODBC link to that? Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Milind Keer" wrote in message ... Hey Charles Thanks again for the prompt reply. In my Access I am not downloading complete DB, What I am doing here is I am using ODBC (& data source) Connection to link the database. By getting doing this I can see all the data but I can't really add new fields to this table because this is just a view. Now to get a local copy of Data i have already tried Make Table query but after some time getiing error "To many records" :( That is why I said I can't really copy data on my local copy? And because of this I can't add new field and can't get that new field in Pivot table.... Do you think is there any other way handling this issue?? I m thinking of using SQL client instead of MS Access, will that solve the problem?? Please advise. Thanks again!! Milind Keer |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can we connect Pivot to multiple source data (MS Access)
Oh!! Does that mean I can write a query and can add extra table in my view
table itself? Interesting.... I will try to do that?? Hope this will not impact original DB?, I don't think so but just wanted to make sure.... Sorry as I said earlier I am not DB guy :) Thanks again!! I will try this and if required I will get back to you... Thanks! Milind Keer "Charles Williams" wrote: Why can't you create a query on the Access server that produces the old and new columns you want and then use the ODBC link to that? Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Milind Keer" wrote in message ... Hey Charles Thanks again for the prompt reply. In my Access I am not downloading complete DB, What I am doing here is I am using ODBC (& data source) Connection to link the database. By getting doing this I can see all the data but I can't really add new fields to this table because this is just a view. Now to get a local copy of Data i have already tried Make Table query but after some time getiing error "To many records" :( That is why I said I can't really copy data on my local copy? And because of this I can't add new field and can't get that new field in Pivot table.... Do you think is there any other way handling this issue?? I m thinking of using SQL client instead of MS Access, will that solve the problem?? Please advise. Thanks again!! Milind Keer |
Reply |
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) |