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