Access 2010 Median Group qry

[sharkchaser]

tblHistorical2012
Fields in table
City text
ClosingDate Date: Format([ClosingDate],'yyyy')
ClosePrice Currrency
BuildingSize LongInteger

Records in Table (478,795)

I need to calculate the MEDIAN of [ClosePrice] and
[buildingSize] for each city for years 1998 through 2012

GroupBy City and Year

The query would display as below:

City ClosingYear MedianPrice MedianSize
Aliso Viejo 1999 214500 1381
Aliso Viejo 2000 242250 1400
Aliso Viejo 2001 276000 1402
Aliso Viejo 2002 324000 1436
Aliso Viejo 2003 380000 1428
Aliso Viejo 2004 500000 1400
Aliso Viejo 2005 539000 1400
Aliso Viejo 2006 548000 1400
Aliso Viejo 2007 524900 1436
Aliso Viejo 2008 431000 1400
Aliso Viejo 2009 395000 1400
Aliso Viejo 2010 380000 1389
Aliso Viejo 2011 353892 1401

Once the query runs I would like to Export the results as displayed to Excel (2010) to run another set of percent calculations.

I've seen a few examples for group median queries in a Google search (see below link)but can't quite understand what it all means.

ACC2000: Reporting the Median Value of a Group of Records

Thanks . . .

Rick

 

[MajP]

Select 
 Year([ClosingDate]) As YearClosed, 
 City,
 Dmedian("ClosePrice","tblHistorical2012","Year([ClosingDate]) = " & Year([ClosingDate]) & " AND City ='" & City & "'") as MedianPrice,
 Dmedian("BuildingSize","tblHistorical2012","Year([ClosingDate]) = " & Year([ClosingDate]) & " AND City ='" & City & "'") as MedianSize
From tblHistorical2012 
Group by
  City,
  Year([ClosingDate])

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

  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

 

[sharkchaser]

Thanks very much MajP

I can assume that the smaller first code is for a SELECT query.

The second larger code is a Function().

Where do I place that section of code?

More importantly, how do I call it.?

Would love to get this to work.

SharkChaser

 

[MajP]

Place the function in a standard module. The function just makes things very portable, it can be used in a query or code or calculated control. If you look at the function you can see the sql that you can use to roll your own query as well.
The first code is an example of using the function within a query. It shows how to create a calculated field/s using the function.
The function needs to be passed the field to find the median, the query or table name, and an optional criteria.
So in the query
Dmedian("ClosePrice","tblHistorical2012","Year([ClosingDate]) = " & Year([ClosingDate]) & " AND City ='" & City & "'") as MedianPrice

This says create a field name MedianPrice by returning the median of the field closePrice in the table tblHistorical 2012 where the closing date year of that records closing date and the city is that records city.

 

[sharkchaser]

MajP . . . I'm not sure how I did it but it is working well.

I was able to export the results over to Excel and apply my percent calculations.

If you wish to see how I use this data you can view

http://www.propertieswithstyle.com

- click on City Info Pics & Value Charts and scroll down to the Median chart. Because of your assistance I'm going to add 2012 to all of these charts.

I sure wish I could understand this code better.

Thanks again MajP.

Rick

 

[MajP]

To understand this use a simple example of a table (tblHomes) with a bunch of homes and closing prices (closingPrice) without any kind of grouping.

Lets assume the table has 10 records

100
150
125
200
175
110
170
145
195
115

if we call the function
dmedian("closingPrice","tblHomes)

strSQLx = "SELECT TOP 50 PERCENT " & Expression & " FROM " & Domain
strSQLx = strSQLx & " WHERE NOT " & Expression & " IS NULL"
strSQLx = strSQLx & " ORDER BY " & Expression

StrSQLX would resolve to
"Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice"

this would return
100
110
115
125
145

StrSQLy = strSQLx & " DESC"

So this would resolve to
"Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice DESC"
And return
200
195
175
170
150

If you grab the max from the first group and the min from the second group and divide by 2 you have the median. If the original set of values was an odd number of values, then the max and min value would be the same value appearing in both groups. If you add the same number to itself and divide by 2 you would get the same number.


so this line

strSQL = "SELECT (Max(X." & Expression & ")+Min(Y." & Expression & "))/2 AS Median FROM " & strSQLx & " AS X, " & strSQly & " AS y "

Would resolve to

Select
Max(Max(X.ClosingPrice) + Min(Y.closingPrice))/2 AS Median
from
(Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice) AS X,
(Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice DESC) as Y

If you are not familar with subqueries then you would have to read up on them.
So the above query uses two subqueries which are the top and bottom half of the values. It grabs the max from one and the min from the other