Median/Can Access Calculate??

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I cannot figure out how to calculate the median of a set
of numbers in Access. It is a simple formula in Excel
however, I don't see any syntax for it in Access, can
anyone help?? My recordset is too large for Excel to handle
Thanks-Mike
 
Hi Mike

The Median can be calculated as follows:
tName$ = table name
fldName$ = field name you wish to perform the calculation.
Hope this is of some help to you.

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
 
Wow!!
Thanks for your help on this, I really appreciate it!!
-----Original Message-----
Hi Mike

The Median can be calculated as follows:
tName$ = table name
fldName$ = field name you wish to perform the calculation.
Hope this is of some help to you.

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



I cannot figure out how to calculate the median of a set
of numbers in Access. It is a simple formula in Excel
however, I don't see any syntax for it in Access, can
anyone help?? My recordset is too large for Excel to handle
Thanks-Mike
Click to expand...

.
Click to expand...
 
Back
Top