Access NEEDS to have a median function.

[Guest]

Anyone who does reporting of descriptive statistics will agree that it is
imperative for Access to have a median function. It is pretty embarrassing
for Microsoft to not have included this. This almost convinced me to stop
using Access to do my descriptive statistics, even though my data would still
be stored in Access. I know that there is code out there to add a function
to a module to do medians. But even this code does not make it as simple to
do a median as it is for means.

PLEASE add a median function with the next version of Access.

 

[Rick B]

This is a newsgroup, not a suggestion box.

Send your suggestion to

(e-mail address removed)

 

[Douglas J. Steele]

Why? What difference does it make whether it's a function you've written in
a module, or a built-in one?

 

[StCyrM]

Good evening

Whenever you need some function that is not available in Access, you simply get
creative and roll your own. The language structure in Access allows you to
easily do that, however you do have to be able to code.

Here's the one I wrote:

Best Regards

Maurice St-Cyr
Micro Systems Consultants, Inc.



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

 

[Gary Walter]

Hi Maurice,

I mean no offense, but please adjust your code
when you post this to:

Set ssMedian = MedianDB.Openrecordset("SELECT [" & fldName$ & _
"] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS " & _
"NOT NULL ORDER BY [" & fldName$ & "];")

Or, adjust to Mr. Spencer version in other thread.

Thank you.

Again, no offense intended.

Good evening

Whenever you need some function that is not available in Access, you simply get
creative and roll your own. The language structure in Access allows you to
easily do that, however you do have to be able to code.

Here's the one I wrote:

Best Regards

Maurice St-Cyr
Micro Systems Consultants, Inc.



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

Anyone who does reporting of descriptive statistics will agree that it is
imperative for Access to have a median function. It is pretty embarrassing
for Microsoft to not have included this. This almost convinced me to stop
using Access to do my descriptive statistics, even though my data would still

be stored in Access. I know that there is code out there to add a function
to a module to do medians. But even this code does not make it as simple to
do a median as it is for means.

PLEASE add a median function with the next version of Access.