Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2010
    Location
    London
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have created a pivottable based on a Table with 80,000 rows and 20 fields. My 1GB Access database allows me to create the pivottable but seems very sensitive to the fields. On Fields where there are such a few different possible entries eg Dept1, Dept2 or Dept3, I can add this to the row area of the pivottable very quickly. However, when I try adding the vendor field which is different for almost every record (ie around 80,000 different vendors), then the PivotTable generally does the eggtimer thing pretty much indefinitely. It seems bit random but sometimes it comes back with the required pivottable after testing my patience to perhaps several hours. Sometimes I just give up and crash the database. This is a painful experience.

    I've googled and it seems PivotTables are meant for OLAP structured databases rather than relational ones like Access - something to do with treating the data as multi-dimensional tables rather than tables. OLAP is accroding to Wikipedia, 1000 times faster for complex queries than Access relational databases. This is certainly noy my experience.

    Irritatingly, my colleagues who stay in excel assure me that Pivottables are very stable and nvery hang or take more than a few seconds to do pretty much anything.

    I am using 4GB RAM, have 2.5GB spare on my 40GB hard disk and dual core 2.7GHz processors. I dont think I'm hardware constrained.

    Please tell me whether I should give up on this feature of Access for large amounts of data - and switch back to excel. If not, please could you tell me how to use them on large tables without wating huge amounts of time. I'd also be interested if you can tell me what is happening under the hood so I can try to make sense of this.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If your Access database is 1GB before you run the pivot table, then your issue is probably associated with hitting the 2GB limit of an Access database. But in truth, the pivot table in Excel works much better that in Access, and we generally advise users to take that approach - export the data out to Excel and then create the pivot. A couple of pertinent questions as well. What version of Access are you working with, and is your database a split front-end back-end design?
    Wendell

  3. #3
    New Lounger
    Join Date
    Aug 2010
    Location
    London
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. I am using Access 2007 and its not split. I wish Microsoft would not compromise Access with substandard functionality that has wasted a week of my time - when I could have taken the xcel route with the Pivot Table straight away if they didnt seem to be pushing Pivots in Access.

    My major other gripe is the fact that they removed visible descriptions for tables, queries, forms etc when moving from 03 to 07. This was a useful documenting facility in a very hard to document product.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by rupert kenna View Post
    My major other gripe is the fact that they removed visible descriptions for tables, queries, forms etc when moving from 03 to 07. This was a useful documenting facility in a very hard to document product.
    Descriptions are not completely gone, but you need to go to the Properties to see them..Right click Properties
    [attachment=89813:TableProperties.gif]

    They also appear if you use the Documenter. But I agree that it is a pity you can see them listed in the Navigation pane, the way you could in the Database window.
    Attached Images Attached Images
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •