Calculate Median in Access Query

[kjo1080]

Does anyone know how to calculate the median in an Access Query? there is a formula for average but not for median. Does anyone know of a way to do this or of how to write a formula in a module that will work?

Any help is appreciated!

Thanks,

kjo1080

 

[giacomo]

oh well, like I said air code... it should have been

if strWhere <> vbNullString then 
        strSQL = strSQL & " WHERE " & strWhere 
end if

Giacomo

 

[kjo1080]

That Worked!! Here is the final code:

Public Function MedianOfRst(RstName As String, fldName As String, Optional strWhere As String) As Double
     '** Call function in query by Median: MedianOfRst("Table Name","Field Name","[Group]= " & [Group])
     
     'This function will calculate the median of a recordset. The field must be a number value.
     Dim MedianTemp As Double
     Dim RstOrig As Recordset
    
     '** NEW CODE ADDED BY GIACOMO **
     Dim strSQL As String
      
     strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"

     If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & strWhere
     End If

     Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
     '** END OF NEW CODE **


     ' ** OLD CODE REPLACED IN THE BLOCK ABOVE **
     'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
      
     ' ** ORIGINAL CODE **
     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

Again, I really appreciate your help!!

-kjo1080

 

[mngold1]

Dear giacomo,

My numeric field name (which, by the way, is in currency format) is called "Total Cost". Perhaps because the field name has an embedded blank in it, I am getting the runtime error 3075 ("Syntax error (missing operator) in query expression in query expression 'Total Cost' "). When I type brackets around this fieldname, I get the same error with double brackets around the field name in the error message (i.e. [[Total Cost]] ).

What is the fix for this? I am running MS Access 2003.

 

[xenou]

Hmmm,
What is the actual output here:

strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"
[COLOR="Blue"]Debug.Print strSQL[/COLOR]

 

[funkykizzy]

Greetings! I am trying to do the same with Median, Mode, Geometric Mean, and the 5th and 95th Percentiles of a large dataset. I have two levels of organization in my dataset that I need to keep intact: Method and Analyte. I then want to calculate the aforementioned statistical values from a third column (Concentration) with a Make Table Query and it is just not coming out right. I am first trying to accomplish this with just the median using the code in this discussion as my guide. I tried to give each analyte in each method a unique number (i.e. group number above) but I keep getting Run Time Error '3601' on the line

   Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Any thoughts??

Many thanks in advance!!

 

[SydneyGeek]

Without seeing the SQL that you tried to turn into a recordset, it's hard to give a useful answer.

However, you don't need to use VBA; the median across groups can be calculated using a couple of queries. See the last post in this thread.

Denis

 

[funkykizzy]

Hi Sydney!

I would love to show you the SQL that I tried but I was unable to save my work when I shut everything down for the weekend (something about a missing 'object' prevented me from being able to save). I will be attempting this again from scratch...

Thanks for the link!

Unfortunately, I was just getting my Excel VBA "sea-legs" when I discovered that I will need to translate everything I did for the dataset in Excel into Access. I am having a little trouble making the bridge between the two programs so I deeply appreciate your help.

Thank you again!

 

[funkykizzy]

OK, I tried to recreate where I left off and got the following SQL for my query:

SELECT DISTINCT AVERAGED.METHODCODE, AVERAGED.ANALYTE, AVERAGED.[AvgOfRESULT-NUMBER] INTO ANALYTE
FROM AVERAGED
GROUP BY AVERAGED.METHODCODE, AVERAGED.ANALYTE
HAVING (((AVERAGED.[AvgOfRESULT-NUMBER])=MedianOfRst("AVERAGED","AvgOfRESULT-NUM",1)))
ORDER BY AVERAGED.METHODCODE, AVERAGED.ANALYTE;

And here is my module "Median" which is the same as posted above:

Public Function MedianOfRst(RstName As String, fldName As String, Optional strWhere As String) As Double
     '** Call function in query by Median: MedianOfRst("Table Name","Field Name","[Group]= " & [Group])
     
     'This function will calculate the median of a recordset. The field must be a number value.
     Dim MedianTemp As Double
     Dim RstOrig As Recordset
    
     '** NEW CODE ADDED BY GIACOMO **
     Dim strSQL As String
      
     strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"

     If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & strWhere
     End If

     Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
     '** END OF NEW CODE **


     ' ** OLD CODE REPLACED IN THE BLOCK ABOVE **
     'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
      
     ' ** ORIGINAL CODE **
     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

Thank you!

 

[funkykizzy]

...

 

[funkykizzy]

I'm getting a different error with this second attempt...this time I am getting (from the Access Help file):

You tried to execute a query that does not include the specified expression <NAME>as part of an aggregate function. (Error 3122)

You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.




Possible cause:

• You did not enter an aggregate function in theTRANSFORMstatement.

Any thoughts? I know I am missing something, but I am not exactly sure what it is...