Using PivotTable's in Access 2007 with large databases (~200 MB)

Question

Hi guys,

I'm trying to use Access to help me summarize scientific data - temperature and humidity over the past 30 years or so.

The databases are quite large - approximately 200 megabytes each.

The ideal for me would be to use a pivot table to perform the summaries for me, but I'm encountering the problem that every time I try to modify one of the pivot table parameters, i.e. row, column, filter or data set, it spends about a minute thinking about it and sometimes crashes.

I'd like to be able to specify exactly what I want in the pivot table, and THEN tell it to do the processing, rather than have it attempt to process after each step.

Any help would be much appreciated.

Thankyou,

Alex

Answer 1

A few considerations about your data:

1. Are the tables indexed? Indexes speed up query execution
2. If there are several tables, have you created the appropiate relations?

A 200 MB database should not be hard to handle with Access.

Now, about your problem: Use Crosstab queries.

Example: If your table (tblCityTemp) has the following data

city | obsDate    | temp
========================
MTY  | 01/01/2010 | 25
MTY  | 01/02/2010 | 28
MTY  | 01/03/2010 | 30
MX   | 01/01/2010 | 15
MX   | 01/02/2010 | 17

You can use the following query:

TRANSFORM Avg(temp) AS avgTemp 
SELECT obsDate 
FROM tblCityTemp 
GROUP BY obsDate 
PIVOT city;

The output is:

obsDate    | MTY | MX
========================
01/01/2010 | 25  | 15
01/02/2010 | 28  | 17
01/03/2010 | 30  |

Fields city and obsDate must be indexed.

If you want to summaryze more fields, create a crosstab query for each field.

Hope this helps you.