Solved

Improving performance Pivot Table MS Access2007

Posted on 2009-05-18
3
337 Views
Last Modified: 2013-11-29
Hi experts.

I have created a pivot table from a datasheet form consisting of more than 900 000 rows (very large database). The result is that the performace of the pivot table is poor, for instance sometimes the pivot table opens when clicked while other times it will not open (just begins to load but do not finish loading). This also depends on the computer I use.

Can anyone suggest me how to improve performance of my pivot table?

I have been thinking of exporting the datasheet form to excel, make a pivot table in excel and import it back to Access07. Is this  possible and will it improve performance?

Best regards,

Stayer
0
Comment
Question by:Stayer
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24415273
Stayer,

1. The fastest way to speed up your Pivot table is to NOT base it on a table with all the fields.
Create a query with only the fields you need.
Then create the Pivot table from this query.

2. Investigate CrossTab queries.
In many cases they can display the same summarized data as a Pivot Table

3. <I have been thinking of exporting the datasheet form to excel, make a pivot table in excel and import it back to Access07. Is this  possible>
If you are asking if you can import an Excel Pivot table into Access, then the answer is No.

JeffCoachman
0
 

Author Closing Comment

by:Stayer
ID: 31582529
Thank you very much for good tips! I went for nr. 1 and now it works perfectly.

rgds

Stayer
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24416332
Oh Yeah!
;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now