troubleshooting Question

Access -- calculate "median" and "mode"

Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America asked on
Microsoft AccessVisual Basic Classic
19 Comments1 Solution4666 ViewsLast Modified:

Please find attached two files:  
1. "Example Data -- Statistics" (Excel)
2. "Mean, StDev, Var" (Access)

The Excel file contains the following five stats:
- Mean/Average
- StDev
- Var
- Median
- Mode

I've imported the 10 values (B1:B10) into Table1 of the Access file.   In Query1, the mean, stdev, and variance can be easily derived.

My question:  How can I compute/calculate the **median** and **mode** in the database?  

Note: Please keep in mind that I'm currently (in the actual database) using a SQL statement that re-generates queries for other input.   That said, if VBA is required for the calculation of these two statistical measures of dispersion, I need to be able to "call that function" from within Query1 in order to dynamically create/update other queries.

Ideally, any proposed solution includes a working query1 that includes these two measures.

Thank you for your assistance in advance.
Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros