Pivot table with 3 million records

Topic

New Reply

Good day/evening. I have been using Excel pivot tables since they came out. But I have a new challenge that Excel can’t handle. I have a table with 3 million records the boss wants in a pivot table as soon as possible. I’m not necessarily asking for step by step help – I’ll take it if someone has the time – but I need to help in some form so I can get up to spped on creating pivot tables (crosstab queries) in Access. I have both Access 2003 and 2007 available on our system. Thanks for any help.

Jim

Reply | Quote

Replies

You can create a pivot table in Excel based on a table in an Access database (or other external source) without importing the data into Excel. To do so, select the second option (External data source) in step 1 of the Pivot Table Wizard, click Next, then click Get Data in step 2. Once you have selected the data source, the rest is as usual.

In Access, you have crosstab queries and pivot tables.

Crosstab queries are relatively easy to create – there’s a wizard for them (click New in the Queries section of the Database window, then select Pivot Table Wizard) – but static: you have to modify the design of the query to alter the result.

Pivot tables are interactive, but more difficult to design. See for example Designing your first PivotTable/PivotChart views in Access.

Reply | Quote

As always, Hans, I am grateful for your suggestions. I’ll get to work on it. Have a great weekend.

Jim

Reply | Quote