1

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

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Alex
  • 11
  • 1
  • 2
  • Do you mean crosstab queries, and if not, have you considered crosstab queries? – Fionnuala Jan 14 '10 at 23:01
  • Managed to get by - discovered that Excel 2007 has over a million rows, which was enough to transfer a selection of my data to Excel where things happen much, much faster. Also discovered that the function I was looking for exists in Excel - a checkbox called 'Defer Layout Update'. It would still be nice to know if such a thing exists in Access though. – Alex Feb 01 '10 at 03:22
  • A long time has passed since this... but maybe I can propose a new solution: Link the Access database with excel using ODBC... you can create Pivot Tables that read data from ODBC connections. – Barranka May 29 '12 at 19:11

1 Answers1

2

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.

Barranka
  • 20,547
  • 13
  • 65
  • 83