Aggregate MEDIAN

[Guest]

Is there any way to create a pseudo aggregate MEDIAN function for a Totals Query? I want to get several median values for the values in a field satisfying different criteria. I am working with >256 values per field (I'm converting an excel spreadseet to Access as it hit the coumns constraint).

Has anyone come accross this problem or know how I define this function as an aggregate function?
Data is say categorised as follows:
Category Value
1 5
1 4
1 2
2 7
2 4
2 1
3 7
3 4
3 12
....
I want the median value for each category:
ie
Category Median
1 4
2 4
3 7

Access help states:

aggregate function
A function, such as Sum, Count, Avg, and Var, that you can use to calculate totals. In writing expressions and in programming, you can use SQL aggregate functions (including the four listed here) and domain aggregate functions to determine various statistics.
ie doesnt include MEDIAN - so how can I do this?

 

[Sam D]

Here's a median function, paste it into a module, then use it just like the
DAvg, DCount, etc.. functions.

Public Function DMedian(Expr As String, Domain As String, Optional Criteria
As String) As Double
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Temp As Double
Dim OrderedSQL As String

'construct SQL
OrderedSQL = "SELECT " & Expr
OrderedSQL = OrderedSQL & " FROM " & Domain
If Criteria <> "" Then
OrderedSQL = OrderedSQL & " WHERE " & Criteria
End If
OrderedSQL = OrderedSQL & " ORDER BY " & Expr & ";"

Set db = CurrentDb
Set rs = db.OpenRecordset(OrderedSQL)

rs.MoveLast
NumRecords = rs.RecordCount
rs.MoveFirst
rs.Move Int(NumRecords / 2)
Temp = rs.Fields(Expr)

If NumRecords / 2 = Int(NumRecords / 2) Then

'there is an even number of records
rs.MovePrevious
Temp = Temp + rs.Fields(Expr)
DMedian = Temp / 2

Else

'there is an odd number of records
DMedian = Temp

End If

rs.Close
db.Close

End Function

In your case you'll need to create a query that just includes each category
once, add the category field to the query grid. Then add a column to the
grid like...

Median: dmedian("value","Yourtablename","category = " & [category])

HTH
Sam

Is there any way to create a pseudo aggregate MEDIAN function for a Totals

Query? I want to get several median values for the values in a field
satisfying different criteria. I am working with >256 values per field (I'm
converting an excel spreadseet to Access as it hit the coumns constraint).

Access help states:

aggregate function
A function, such as Sum, Count, Avg, and Var, that you can use to

calculate totals. In writing expressions and in programming, you can use SQL
aggregate functions (including the four listed here) and domain aggregate
functions to determine various statistics.

 

[Tom Dierickx]

Check out: http://www.data-for-all.com/display.aspx?src=Workshop (about 2/3
down the page)

Cheers,
TOM