Calculate a Median Access 2010 Query

[sross81]

Is there a way to calculate a median in an access 2010 query? I can't seem to find an example.

 

[jdraw]

see post #2 here
http://www.access-programmers.co.uk/forums/showthread.php?t=106785

 

[sross81]

Thank you for pointing me to that link. I didn't see it when I did a search.


I have it working now, but is there a limit to the number of rows that can go through this function? There was 119000 rows returned by the query and I get an overflow error. If I limit my date range it works fine.


My function:

Function MedianF(pQuery As String, pfield As String) As Single

Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single
strSQL = "SELECT " & pfield & " from " & pQuery & " 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

 

[jdraw]

Can you post a copy of your database in mdb (2003) format?

I have 2003 and can not use accdb formatted files.

You can remove anything confidential/personal - I just need enough info to understand and tes t the query and function.

 

[sross81]

I am not sure how to create a access 2003 version. I tried to create a new db and I was just going to copy the tables, query, and function over, but I don't even see an option to save as or create as a lower version.

 

[sross81]

I changed the data types from single to Long and that solved the overflow problem.


Function MedianF(pQuery As String, pfield As String) As Long

Dim rs As Recordset
Dim strSQL As String
Dim n As Long
Dim sglHold As Long
strSQL = "SELECT " & pfield & " from " & pQuery & " 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

 

[jdraw]

So you have a working data base that does what you need now?

 

[sross81]

Yes I'm sorry I thought I said that above. I realized it did not. Yes works perfectly. Thank you

 

[jdraw]

You are welcome, glad to help.