Pivot Table in MS Access 2003

[Frustrated Fed WRKER]

Ok, Federal Govt worker here in a new office. In this office, they use Excel
exclusively to display data using pivot table/ charts. I've seen no one use
Access. To me all this data crunching screams for Access. If I can create a
pivot chart or (query?) in access, which the articles on the web say MS
Access 2003 can do. But I don't have a pivot table option or at least I
can't find it. Then I can make the argument to switch from Excel for data
analysis.

I have 5+ years of Access experience and I've checked with the resident
Access genius and he knows it's possible but can't remember how.

 

[rico]

Its a way of displaying a query, so create a create then, notice next the
button when you which between design and view row, there is a drop down which
allows the query to be displayed in different views, one being pivotchart.

If using vba code then you can use:

Docmd.openquery "QueryName", acViewPivotChart

HTH

 

[Ed Ferrero]

Frustrated Fed WRKER wrote,

Ok, Federal Govt worker here in a new office. In this office, they use
Excel
exclusively to display data using pivot table/ charts. I've seen no one
use
Access. To me all this data crunching screams for Access.

Although you can do this in Access, you may find it better for your users to
continue using Pivot Tables in Excel.

It is possible (and IMO desirable) to maintain a central data repository in
a database stored on a server, and to link Excel Pivot Tables directly to
the server-based data. That is, keep the data in Access (or your favourite
database) but use Excel for reporting.

I would usually build a small number of queries that pull the relevant data
together specifically for use by Excel Pivot Tables.

This provides all the advantages of database design - centrally located
data, good backups, normalized tables.
And the advantages of Excel - interactive Pivot Table design, small user
learning curve.

A useful tip in Excel is that you can build a pivot table using external
data, then set the table options so that data is refreshed when the workbook
is opened, and so that data is not stored in the workbook. Save the workbook
as a template, and get users to download it to their own machines. Users are
then free to modify the Pivot Table report as much as they like and save
specialized reports to their own machine, nobody else is affected.

These are very small files (around 200Kb) even though they can report on
large amounts of data. It is possible to develop a complex Pivot Table for a
new report, save as a template and email the file to a user with no
disruption to the database or to other users.

Ed Ferrero
www.edferrero.com