MS Access 2007: Calculating Mean, Median and Mode

Microsoft Access is a data storage application that offers basic mathematical functions. Access queries (and user-originated text boxes in forms and reports) will do a good job in ordinary “numbers crunching.” Statistical analysis in Access, however, is somewhat rudimentary. The only built-in statistical functions in Access are mean (i.e., average) and standard deviation (a measure of how the numbers in a list are spread out).

Access will not automatically calculate the median (the value halfway through a list of numbers where there are an equal array of numbers below and above). Nor will Access automatically calculate the mode (the most frequently occurring value in a set of data). To obtain calculations in Microsoft Access for median and mode, you need a knowledge of Visual Basic and advanced SQL programming. An easier way is let Microsoft Excel do the heavy lifting for statistical calculations.

This article will demonstrate how to use the built-in Microsoft Access function for mean (avg). Then we will see how we can import an MS Access table into Excel and use Excel formulas for the median and mode.

The avg (mean) function in MS Access will calculate the average of an array of numbers. The screenshots below illustrate how to create a text box on a report that calculates the average amount of sales in a report. (The design and report views are shown.)

click to enlarge

click to enlarge

Note: The syntax for the avg formula in Access is =avg([FieldName]).

The avg function can also be called up in summary queries and tables. The screenshots below illustrate how to design a query that calculates both a sum and an average of sales by areas. (The query design and table views are shown.)

click to enlarge

click to enlarge

♦ Using Visual Basic Programming

There are a number of helpful resources on the web with Visual Basic and advanced SQL code for calculating median and mode. Each resource, however, presupposes a thorough knowledge of VBA and SQL coding. Here are a few resources:

Microsoft Access Office VB VBA Help and Examples - Calculating the Median of a Record Set

Microsoft Help and Support - ACC: How to Use Code to Derive a Statistical Median

dbFormums: Calculating the Mode Value in a Query

♦ Using Microsoft Excel

For the "Visual Basic and SQL impaired" (which includes the most of us), Microsoft Excel is better suited for statistical analysis and requires no special programming skills. Follow these steps to import your numbers from Access and quickly calculate your median and mode (and, if desired the mean or average, too):

1. Open a blank MS Excel 2007 spreadsheet.

2. Click on the Data tab/Get External Data (on the far left next to the Connections group).

3. Select From Access and navigate to the Access database and table (or query) you with to import.

4. The table or query will load into the spreadsheet.

5. Enter the following formulas based on the cells where your numerical data is located:

For Median: =MEDIAN(CELL:CELL*)

For Mode: = MODE (CELL:CELL)

For Mean: = AVERAGE (CELL:CELL)

*Cell Row and column address; e.g. G2:G94

The screenshot below illustrates an imported table from Access with the formulas:

click to enlarge

Understanding the Different Types of Microsoft Excel Functions

Crosstab Queries in Access 2007 (Note: A crosstab query using the Count function will generate the desired results to view the statistical mode.)

Microsoft Office Online - Calculate the median of a group of numbers

Microsoft Office Online - Avg Function