My Access Query for Median seems not working for even records

[zlj0000]

Hi

I want to get a run-time Median Age from my table within the selected timeframe. I am doing this through the next two steps:

1. myQuery is a query to get all the NOT NULL records from myTable:

SELECT myTable.myNumber
FROM myTable
WHERE myDate Between [FromDate] And [ToDate] AND myNumber is not NULL;


2. Get the Median myNumber from myQuery:

SELECT x.myNumber AS median
FROM [myQuery] AS x, [myQuery] AS y
GROUP BY x.myNumber
HAVING (((Sum(IIf(y.myNumber<=x.myNumber,1,0)))>=(Count(*)+1)/2) And ((Sum(IIf(y.myNumber>=x.myNumber,1,0)))>=(Count(*)+1)/2));

I run the queries, and found out that if the number of records in myQuery is odd, then it works fine. But when there is even number of records in myQuery, it returns NULL.


I am new in SQL programming. I got this piece of code from this forum. I can not figure out why the query does not work on even records.

Can anyone help me out? Thank you in advance.

 

[MajP]

not sure if that works, but this is a generic function to return the mean from a query or table. Works on all cases.

Expression: field to get median
Domain: query or table name
Criteria: Optional criteria statement

Public Function DMedian(Expression As String, Domain As String, Optional Criteria As String) As Variant
  On Error GoTo errlbl
  'Special Thanks to strongM on the sql idea
  Dim strSQL As String
  Dim strSQLx As String
  Dim strSQly As String
  
  Expression = "[" & Expression & "]"
  Domain = "[" & Domain & "]"
  
  strSQLx = "SELECT TOP 50 PERCENT " & Expression & " FROM " & Domain
  strSQLx = strSQLx & " WHERE NOT " & Expression & " IS NULL"
  If Criteria <> "" Then
    strSQLx = strSQLx & " AND " & Criteria
  End If
  strSQLx = strSQLx & " ORDER BY " & Expression
  strSQly = strSQLx & " DESC"
  strSQLx = "(" & strSQLx & ")"
  strSQly = "(" & strSQly & ")"
  strSQL = "SELECT (Max(X." & Expression & ")+Min(Y." & Expression & "))/2 AS Median FROM " & strSQLx & " AS X, " & strSQly & " AS y "
  'Debug.Print strSQL
  DMedian = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)!Median
    Exit Function
errlbl:
  If Err.Number = 3061 Then
    Debug.Print "This usually indicates problems in the sql string. Check all field and table names." & vbCrLf & strSQLx, vbInformation, "SQL String"
  Else
    Debug.Print Err.Number & " " & Err.Description & vbCrLf & strSQLx
  End If
End Function

 

[PHV]

Why an UDF ?
I'd try this pure SQL way:

SELECT (Max(X.myNumber)+Min(Y.myNumber))/2 AS median
FROM (
SELECT TOP 50 PERCENT myNumber FROM myQuery WHERE myNumber Is Not Null ORDER BY 1
) X, (
SELECT TOP 50 PERCENT myNumber FROM myQuery WHERE myNumber Is Not Null ORDER BY 1 DESC
) Y

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Why an UDF ?

Utility.

 

[PHV]

Utility{/i]
???

 

[MajP]

Utility n. pl. u·til·i·ty
The quality or condition of being useful; usefulness:

 

[raskew]

Hi -

Try playing with this:

Public 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 hom 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
'*******************************************

Hope that helps -- Bob