Results 1 to 3 of 3
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    large records pivot table (Access 2003)

    I rarely ever use Access but a co-worker had a large file she needed analyzed and it had too many records for Excel (which is what I know best). This .mdb file had about 685K records about various businesses pulled from a public source (DINS numbers, business names, revenue, etc.) and I needed to build a pivot table to analyze the data. I have Win2k and lots of memory on a 2 yr old PC but the system was soooo sllllooooowwww it took many minutes and at one point it failed when I went to add one more field the table. I ended up creating five separate databases but even that was too much for Access. I can't believe I spent nearly an entire work day on this project.

    So my question...
    At what point do you think Access is just too dinky to handle a lot of data? Is "a lot of data" more than 100K records? One of the separate databases I created was 248K (for the US-only data) and it was so slow that it wasn't useable. Even though I was able to create the pivot table (after a very, very long time) every mouse click to traverse it took minutes which effectively made it unuseable.

    I can get an account on our Oracle database which would certainly handle it very fast, but that doesn't give me the built-in pivot table feature (at least not that I know of). Does anyone have any recommendations for what I could use next time this comes up (and it's needed about once/quarter)?

    Thnx, Deb

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: large records pivot table (Access 2003)

    Deb,

    I think that something else may be happening here. I also use pivot tables to help review data and just passed a table with 357K rows of data with 20 data fields per row into the pivot table without issue. That is, the query to generate the dataset took about a minute to run and the process to open the pivot table took about 15 seconds. Refreshing the pivot table took about another minute.

    What is the data source for your pivot table, query, table, or other? If a query, check to ensure it generates the data correctly and the query is not the problem. You could try running a count on the query or use a create table query to figure out how many records are actually being passed. It sounds as if the data source is a query that is not running properly. You might try using a query to populate a table and base the pivot table form on the table rather then the query as I find it runs faster.

    FYI - I have tables with much more data in them and have no issues with access handling it. Also have databases in the 1 gig size with no problems at all. I dont think the amount of data you have is the issue.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: large records pivot table (Access 2003)

    Thanks for the informative reply. The pivot table was generated data from the data table itself, no query. I made the pivot table from the View menu and just dragged the fields over the appropriate slices of the table area. The first time I tried it with the entire record set, 685K records (approximately). When I pulled over a 5th item to the data area, it errored with messages about 'low system resources' or something about maximum # of records 64K (don't remember exact message). Even pulling each item over (for the middle 'data' items) was agonizingly slow (minutes per item).

    I later stripped out all but US data for 275K records and made the pivot table but even displaying that one took many minutes.

    Maybe I should create a query to do this but I don't know how to do that so I'll read up on it. I can do trivial queries like WHERE name="joe" but that's about it.

    Thnx, Deb

Posting Permissions

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