how do I calculate a median in access?

[rsteele]

We are trying to calculate the median value, 25th percentile and 75th
percentile for a list values in access. Or, someone can give direction for
how to use the function in access by tapping into excel.

 

[Douglas J Steele]

Take a look at http://support.microsoft.com/?id=210581

 

[rsteele]

Thanks Doug

I read 210581 and we have done this and it is working. The problem is that
it calculates based on all records and we want it to calculate the median for
a selection based on a query.

We have water quality data and based a a query of years, site and parameter
a chart window displays the min, max, water quality objective. We want it to
also calculate median on the fly and add it to the chart window.

How can we aggregate or calculate from within a query?

 

[Douglas J Steele]

Not quite sure what you mean by "for a selection based on a query".

You can modify the example to accept a Where clause and plug that Where
clause into the definition of the SQL that's used to open ssMedian.

 

[Shawn Sumpter]

Did you get something to work for you? I have the same problem. All
results are stored in a database, monthly reports are created by the user
clicking on a report that is based on a query that askes the user to specify
the month that the report (query) is to be done on. I tried using the code
in the knowlege base but had the same problems you did.

This has to be simply done. Field crew is responsible for collecting and
reporting the data. It has been hard enough to get them to enter data into a
database.

Shawn
City of Healdsburg, CA

 

[myh0052]

Can this Median function be made into as one of the aggregate built-in
functions in Access? If not, what to do in MS Access to use this Median
function in the SQL query design grid with other aggregate functions using
Group By SQL clause? So that the Median function would have same
functionality and produce Median value for each grouped data set not the
whole data set instead.

For Example the table below, the year is the field to group by, the value is
the field that we want to calculate the median for each year.

Table Example
Year value
1964 6
1971 6
1965 550
1972 556
1965 23.786
1964 34
1971 0.2
1972 0.007
1964 0.02
1971 0.05
1971 20
1965 0.58


The desired results we would like to see:

Year Median
1964 6
1965 23.786
1971 3.1
1972 278.0035

At moment, you will have to make table/query for each year. And then use the
Median function to calculate the Median for each table. If you have 30 to 40
years, it is a lot of work in oder to produce the desired result.

 

[Douglas J. Steele]

Try the following (NOTE: I haven't tested this exhaustively, but I did get
the results for which you were looking!)

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

To use get the results for which you're looking, create a query that returns
the unique years. For the sake of argument, save it as qryUniqueYears:

SELECT DISTINCT WhatYear
FROM MyTable


Create a second query based on the query you just saved that uses the
DMedian function I gave above:

SELECT WhatYear, DMedian("WhatValue", "MyTable", "WhatYear = " & [WhatYear])
FROM qryUniqueYears

 

[adsl]

Can this Median function be made into as one of the aggregate built-in
functions in Access? If not, what to do in MS Access to use this Median
function in the SQL query design grid with other aggregate functions using
Group By SQL clause? So that the Median function would have same
functionality and produce Median value for each grouped data set not the
whole data set instead.

For Example the table below, the year is the field to group by, the value
is
the field that we want to calculate the median for each year.

Table Example
Year value
1964 6
1971 6
1965 550
1972 556
1965 23.786
1964 34
1971 0.2
1972 0.007
1964 0.02
1971 0.05
1971 20
1965 0.58


The desired results we would like to see:

Year Median
1964 6
1965 23.786
1971 3.1
1972 278.0035

At moment, you will have to make table/query for each year. And then use
the
Median function to calculate the Median for each table. If you have 30 to
40
years, it is a lot of work in oder to produce the desired result.

 

[adsl]

Try the following (NOTE: I haven't tested this exhaustively, but I did get
the results for which you were looking!)

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

To use get the results for which you're looking, create a query that
returns the unique years. For the sake of argument, save it as
qryUniqueYears:

SELECT DISTINCT WhatYear
FROM MyTable


Create a second query based on the query you just saved that uses the
DMedian function I gave above:

SELECT WhatYear, DMedian("WhatValue", "MyTable", "WhatYear = " &
[WhatYear])
FROM qryUniqueYears


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can this Median function be made into as one of the aggregate built-in
functions in Access? If not, what to do in MS Access to use this Median
function in the SQL query design grid with other aggregate functions
using
Group By SQL clause? So that the Median function would have same
functionality and produce Median value for each grouped data set not the
whole data set instead.

For Example the table below, the year is the field to group by, the value
is
the field that we want to calculate the median for each year.

Table Example
Year value
1964 6
1971 6
1965 550
1972 556
1965 23.786
1964 34
1971 0.2
1972 0.007
1964 0.02
1971 0.05
1971 20
1965 0.58


The desired results we would like to see:

Year Median
1964 6
1965 23.786
1971 3.1
1972 278.0035

At moment, you will have to make table/query for each year. And then use
the
Median function to calculate the Median for each table. If you have 30 to
40
years, it is a lot of work in oder to produce the desired result.

 

[myh0052]

Thank you very much for the info. It did work with the Example table data.
We will try it on the real database data see how it works.

Try the following (NOTE: I haven't tested this exhaustively, but I did get
the results for which you were looking!)

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

To use get the results for which you're looking, create a query that returns
the unique years. For the sake of argument, save it as qryUniqueYears:

SELECT DISTINCT WhatYear
FROM MyTable


Create a second query based on the query you just saved that uses the
DMedian function I gave above:

SELECT WhatYear, DMedian("WhatValue", "MyTable", "WhatYear = " & [WhatYear])
FROM qryUniqueYears


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can this Median function be made into as one of the aggregate built-in
functions in Access? If not, what to do in MS Access to use this Median
function in the SQL query design grid with other aggregate functions using
Group By SQL clause? So that the Median function would have same
functionality and produce Median value for each grouped data set not the
whole data set instead.

For Example the table below, the year is the field to group by, the value
is
the field that we want to calculate the median for each year.

Table Example
Year value
1964 6
1971 6
1965 550
1972 556
1965 23.786
1964 34
1971 0.2
1972 0.007
1964 0.02
1971 0.05
1971 20
1965 0.58


The desired results we would like to see:

Year Median
1964 6
1965 23.786
1971 3.1
1972 278.0035

At moment, you will have to make table/query for each year. And then use
the
Median function to calculate the Median for each table. If you have 30 to
40
years, it is a lot of work in oder to produce the desired result.