MS Access has built-in mathematical functions to crunch numbers. For statistical data, however, the only handy function is the mean (average). Calculating median and mode, however, requires Visual Basic coding. This article shows some easier ways for calculating statistical functions in Access.
slide 1 of 6
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.
slide 2 of 6
Calculating the Mean
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.)
Note: The syntax for the avg formula in Access is =avg([FieldName]).
slide 3 of 6
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.)
slide 4 of 6
Calculating the Median and the Mode
♦ 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:
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: