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:
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: