Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 95918 - Last Review: January 18, 2007 - Revision: 2.3
ACC: How to Use Code to Derive a Statistical Median
This article was previously published under Q95918
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article shows you how to create a Visual Basic for Applications
procedure to open a table, read the data, and find the statistical median.
The median is a measure of central tendency, another "middle" of a data
set, like the mean or average). The data set consisting of the numbers 1,
2, 3, 6, and 100 has a median of 3, the middle of the set. The data set
consisting of the numbers 1, 2, 6, and 10 has a median of 4, the middle
of the set [(2 + 6) / 2 = 4].
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0.
To create a procedure that determines the statistical median of a set
of numbers:
- Create a module and type the following line in the Declarations section if it is not already there:
- Type the following procedure:
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this code in Access Basic.
In Microsoft Access 2.0, 7.0, and 97:
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%
Set MedianDB = CurrentDB()
Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName$ & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x% = RCount% Mod 2
If x% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName$)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x% = ssMedian(fldName$)
ssMedian.MovePrevious
y% = ssMedian(fldName$)
Median = (x% + y%) / 2
End If
ssMedian.Close
MedianDB.Close
End Function
Function Median (tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Snapshot
Dim RCount%, i%, x%, y%, OffSet%
Set MedianDB = CurrentDB()
Set ssMedian = MedianDB.CreateSnapshot("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS _
NOT NULL ORDER BY [" & fldName$ & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName$ & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x% = RCount% Mod 2
If x% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName$)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x% = ssMedian(fldName$)
ssMedian.MovePrevious
y% = ssMedian(fldName$)
Median = (x% + y%) / 2
End If
ssMedian.Close
MedianDB.Close
End Function
How to Use the Median() Function
Create a form and add a text box control where you want to display the
median values of a data set. Set the ControlSource property of the text
box control to the following:
=Median("<TableName>", "<FieldName>")
The value of this control is the median of the data set. Another way
to use this function is to call it from within another function that
compares the median from different data sets. For example,
Function CompareMedians()
Dim MyDB as Database
.
.
.
X = Median("<TableName>", "<FieldName>")
Y = Median("<TableName>", "<FieldName>")
If X > Y Then Debug.Print "The median for X is greatest."
End Function
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
| kbhowto kbprogramming KB95918 |
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please