Solved

Improving performance Pivot Table MS Access2007

Posted on 2009-05-18
3
336 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 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

9 Experts available now in Live!

Get 1:1 Help Now