Group by for median

[leeb91]

Hi, I am using the function below to find the median value of records but the problem is that I am trying to group by multi-levels in a query. For example, I have three other fields "Account_Name", "Claim_Status", "Disease_Category" that I need to take into account when calculating median value. I found it easy to get an average value of the records via total row feature in query design but there is nothing for median. Here is the SQL view of my query.
I want the Median_Indemnity to have the same functionality as the field "AvgOfIndemnity_Paid".
Any help would be greatly appreciated

SELECT FINAL_FOR_DB.Account_Name, Count(FINAL_FOR_DB.Claim_Status) AS CountOfClaim_Status, FINAL_FOR_DB.Claim_Status, Avg(FINAL_FOR_DB.Indemnity_Paid) AS AvgOfIndemnity_Paid, MedianF("FINAL_FOR_DB","Indemnity_Paid") AS Median_Indemnity, FINAL_FOR_DB.Disease_Category
FROM FINAL_FOR_DB
GROUP BY FINAL_FOR_DB.Account_Name, FINAL_FOR_DB.Claim_Status, MedianF("FINAL_FOR_DB","Indemnity_Paid"), FINAL_FOR_DB.Disease_Category
HAVING (((FINAL_FOR_DB.Claim_Status)="SETTLED"));




Function MedianF(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 fieldname1= '" & pgroup & "' and fieldname2='" & 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
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

 

[Micron]

[I want the Median_Indemnity to have the same functionality as the field "AvgOfIndemnity_Paid".]

Not sure what you mean by that. It looks to me that you have correctly constructed the query except I wonder about grouping by the function -
GROUP BY FINAL_FOR_DB.Account_Name, FINAL_FOR_DB.Claim_Status, MedianF("FINAL_FOR_DB","Indemnity_Paid").

I would use the alias you have already created in the select portion of the sql ( Median_Indemnity ) in case your construct calls the function again. It also appears that your issue is grouping, but you don't say what's wrong with the result you get. Keep in mind that grouping will be by order of the fields listed. However, if I am correct that the median is a single number from a population, you cannot group on what may very well be a unique value. You might need to move the median function to an update query that uses either the select portion of what you have as a sub query, or against a table that is populated with the data after you have converted the select to an append.

 

[leeb91]

The problem is that the median is calculated frm the entire records and they are not grouped by other fields like "Account_Name", "Claim_Status", "Disease_Category"

Just like you can add avg in the total row and still be grouped by other fields , I would like to do the same for median function. The Sql code above allows me to group the data by Account Name, Disease Category , and Claim Status first and calculate the avg within each sub group. However, when I use median function, it will just give me the median value of all values in the indemnity cost which should be different for each sub group.

 

[Micron]

Think I understand that you want to determine the median over sub-sets of the entire population, but want to return the median along with fields for each group. Without knowing how many sub-sets you have or may need to expand to in the future, what comes to mind is using a union query that uses the function to return the value for each group (3 groups requires two union statements). So
sqlPart1 including function call with arguments
UNION
sqlPrt2 including function call with arguments
UNION
sqlPrt3 etc.

Probably won't be the answer you're looking for, but I'm afraid it's all I've got at this point. Good luck.