Help w/Median

[sluggercoach]

I have read through the forum and found many posts on how to calculate median. However, I am still having trouble. I am attempting to calculate median cycle times per station for an assembly. The result should look like this:

F0100 2.56
F0400 5.87
F0425 9.09
etc, etc.

Here is the code I am using. I am calling it from a query (SQL shown below). The result is that my stations are displayed but the median is for all of the values for all station displayed over and over. I cannot get it to show the median for each specific station. Any suggestions on what I am doing wrong?

Code

Function MedianT(pTable As String, pfield As String, Optional pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight",[Value of Grouping field]) <enter>
'Output: 41.36 (may vary according to how much
' you've fiddled with this table).
'*******************************************
Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single
If Len(pgroup) > 0 Then
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements
MedianT = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianT = sglHold / 2
End If
rs.Close
End Function

Query SQL

SELECT mtest.Station, medianT("mtest","num") AS MEDIAN
FROM mtest
GROUP BY mtest.Station;

Result

MedianStationMEDIAN
3.41666674613953F01003.41666674613953F01303.41666674613953F01403.41666674613953F01603.41666674613953F01803.41666674613953F02103.41666674613953F02303.41666674613953F02503.41666674613953F02703.41666674613953F02903.41666674613953F03103.41666674613953F03303.41666674613953F03603.41666674613953F04103.41666674613953F04303.41666674613953F04503.41666674613953F04703.41666674613953F04903.41666674613953F05303.41666674613953F05603.41666674613953F05903.41666674613953F06103.41666674613953F06303.41666674613953F06503.41666674613953F07103.41666674613953F07203.41666674613953F07403.41666674613953F07903.41666674613953F08203.41666674613953F08603.41666674613953F08703.41666674613953F08903.41666674613953F09003.41666674613953F09203.41666674613953F09403.41666674613953F09703.41666674613953F10003.41666674613953F10203.41666674613953F10403.41666674613953F10603.41666674613953F11003.41666674613953F11303.41666674613953F11503.41666674613953

 

[sluggercoach]

Sorry. That result should look like this:

F0100 3.41666674613953
F0130 3.41666674613953
F0140 3.41666674613953
F0160 3.41666674613953
and so on...

 

[georgedwilkinson]

Sorry, I just re-read and that's definitely not going to help.

The issue here is the group by functionality and the fact that Bob's code was written with the assumption that there is a field called "type" in the table.

Now, if you have a field called type in table that will help, that would be good. But the column "Station" is the one you need to worry about.

So, you need to change Bob's code from "where type" to "where Station" and pass in the value of "station" into the function (I think).

 

[raskew]

Hi-

That's interesting. Although it's obviously my code, can't find it anywhere in my files or this forum. Here's what I have:

Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight") <enter.
'Output:    41.36 (may vary according to how much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

Bob

 

[sluggercoach]

The code was certainly Bob's from a post on this site. I apologize if I wasn't allowed to use it. I thought if it was posted I was ok.

I got it to work now with station but I also need to group by shift. I tried changing the code to have a 2nd group by item (I called it pgroup2) but I cannot get it too work. This should be the last thing I need to get working. How would I add a 2nd group by item in the function? Here is what I tried but I think I goofed it up.

Function MedianM(pTable As String, pfield As String, Optional pgroup As String, Optional pgroup2 As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight",[Value of Grouping field]) <enter>
'Output: 41.36 (may vary according to how much
' you've fiddled with this table).
'*******************************************
Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single
If Len(pgroup) > 0 Then
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and Station = '" & pgroup2 & "' and " & pfield & ">0 Order by " & pfield & ";"
Else
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements
MedianM = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianM = sglHold / 2
End If
rs.Close
End Function

 

[raskew]

Hi -

Far as I'm concerned, if the code is posted here, it's yours to use. If it's copywrighted or 'secret', then it stands to reason it shouldn't be posted.

Still not quite sure what I was thinking when I created that code. Is it possible to post a small example of your groups and your actual calling code.

Thanks - Bob