Calculate Median with Conditions

rrstudio2

I am using the following vba code to calculate the median of a table
in MS Access:

Public Function MedianOfRst(RstName As String, fldName As String) As
Double
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp
End Function

While it works great for calculating the median of an entire row in a
table, I now have the need to calculate the median of several subsets
of data in a table. Say we have a table:

Shift ProcessingTime
Day 2
Night 1
Day 3
Day 5
Night 6
Night 9

I need to be able to return the median processing time of orders
processed during day and during the night. I know I could run 2
queries, one to split the table into day and night orders, then use
the median function, but my real project requires the data to be split
up into 8 categories so this would be rather time consuming and
involve a lot of sub-queries. Any ideas on how to modify the above
query to only calculate the median if another cell in the row meets
certain criteria?

Thanks,
Andrew V. Romero

Jul 29 '08 #1

Marshall Barton

rr*******@icqmail.com wrote:

>I am using the following vba code to calculate the median of a table
in MS Access:

Public Function MedianOfRst(RstName As String, fldName As String) As
Double
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp
End Function

While it works great for calculating the median of an entire row in a
table, I now have the need to calculate the median of several subsets
of data in a table. Say we have a table:

Shift ProcessingTime
Day 2
Night 1
Day 3
Day 5
Night 6
Night 9

I need to be able to return the median processing time of orders
processed during day and during the night. I know I could run 2
queries, one to split the table into day and night orders, then use
the median function, but my real project requires the data to be split
up into 8 categories so this would be rather time consuming and
involve a lot of sub-queries. Any ideas on how to modify the above
query to only calculate the median if another cell in the row meets
certain criteria?

You can use another argument to specify the grouping field:

Public Function MedianOfRst(RstName As String, _
fldName As String, _
grpName As String) ...

Then you can use the recordset's Filter property to restrict
the function to that one field:
. . .
RstOrig.Sort = fldName
RstOrig.Filter = grpName
. . .

The query would look like:

SELECT Shift,MedianOfRst("table","ProcessingTime","Shift" )
FROM table
GROUP BY Shift

--
Marsh

Jul 29 '08 #2

rrstudio2

I gave this a try (with the table name of ShiftTable), but it doesn't
appear to be working. I changed the function code to:

Public Function CondMedian(RstName As String, fldName As String,
grpName As String)
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
RstOrig.Filter = grpName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
CondMedian = MedianTemp
End Function

and created a query with the following sql:

SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift")
FROM ShiftTable
GROUP BY Shift;

when I run this query, it returns:
Day 4
Night 4

so it appears to be taking the median of the whole recordset and not
grouping by Day and Night Shift. I want the unique median for the day
and the unique median for the night shift. Any other ideas?

Thanks,
Andrew V. Romero

Jul 29 '08 #3

Marshall Barton

rr*******@icqmail.com wrote:

>I gave this a try (with the table name of ShiftTable), but it doesn't
appear to be working. I changed the function code to:

Public Function CondMedian(RstName As String, fldName As String,
grpName As String)
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
RstOrig.Filter = grpName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
CondMedian = MedianTemp
End Function

and created a query with the following sql:

SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift")
FROM ShiftTable
GROUP BY Shift;

when I run this query, it returns:
Day 4
Night 4

so it appears to be taking the median of the whole recordset and not
grouping by Day and Night Shift. I want the unique median for the day
and the unique median for the night shift. Any other ideas?

Boy, did I muck that up. That should have been more like:

SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift="
& Shift)
FROM ShiftTable
GROUP BY Shift

--
Marsh

Jul 29 '08 #4

rrstudio2

I gave that a try and it comes back with a runtime error saying Too
few parameters. Expected 2. If I debug, it brings me to the line:

Set RstSorted = RstOrig.OpenRecordset()

Any other things I should try?

Thanks,
Andrew V. Romero

Jul 29 '08 #5

rrstudio2

Ah ha, changing the query to:

SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift='" & Shift
& "'")
FROM ShiftTable
GROUP BY Shift;

worked. Notice the ' that I added before and after the Shift variable
argument. I think this may save me a lot of time. Thanks for your
help. I wish I know some of these tricks better.

-Andrew V. Romero

Jul 29 '08 #6

Marshall Barton

rr*******@icqmail.com wrote:

>Ah ha, changing the query to:

SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift='" & Shift
& "'")
FROM ShiftTable
GROUP BY Shift;

worked. Notice the ' that I added before and after the Shift variable
argument.

The quotes are needed because the Shift field is apparently
a Text field. A number type field would not accept the
quotes.

--
Marsh

Jul 30 '08 #7