The data contained in an Access database can be analysed using Excel and PivotTables. The data needs to be exported to an Excel file first of all.
If the database contains records from Lookup columns the actual data exported is the index into the appropriate table not the actual data that would be seen by looking at the record in Access. A workbook needs to be created that contains

• a worksheet of the data originally exported
• worksheets containing data from all the lookup tables used by the originally exported data
• a merged worksheet containing data that had been entered directly into the exported data and data derived from the lookup tables by use of the indices stored in the originally exported data records

For the merged worksheet, the data needing to be copied directly from the originally exported database is copied using the simple Excel statement

=(worksheetid!cell number)

=LOOKUP(cellnumber,lookuptableworksheet!range1,lookuptableworksheet!range2)

• cellnumber is the cell in the merged worksheet that contains an index into the appropriate lookup table
• lookuptableworksheet!range1 is the worksheet containing records exported from the appropriate lookup table and range1 is the column containing index values.
• lookuptableworksheet!range2 is the worksheet containing records exported from the appropriate lookup table and range2 is the column containing values related to the index values.

The idea is that the LOOKUP function will search lookuptableworksheet!range1 to find a value that matches the index value stored in the records originally exported from the database and return the required vale, lookuptableworksheet!range2.

The merged worksheet forms the basis for PivotTable analysis.