basMedian obtaining quarterly results

  • Thread starter Thread starter rdcsfd
  • Start date Start date
R

rdcsfd

I'm using the following query to get quarterly results for each bedroom
number. The median function is returning the same number for each bedroom
number in each quarter. Can someone tell me how to get it to provide the
median price by bedroom number?

SELECT DISTINCTROW Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy')
AS [Quarter], Sales2.[Property Subtype 1], Sales2.Bedrooms AS [Bedrooms],
Avg(Sales2.[Selling Price]) AS [Average Price],
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') &
"'")),'$#,###') AS [Median Price], Min(Sales2.[Selling Price]) AS [Lowest
Price], Max(Sales2.[Selling Price]) AS [Highest Price], Count(*) AS [# Sold]
FROM Sales2
WHERE Sales2.City = 'Santa Rosa' AND Sales2.[Property Subtype 1] = 'Cond'
GROUP BY Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy'),
Sales2.[Property Subtype 1], Sales2.Bedrooms, Year([Sales2].[Selling
Date])*4+DatePart('q',[Sales2].[Selling Date])-1;

Here are the results from the above query:
City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $375,000
Santa Rosa Q1 2003 COND 2 $238,704.17 $375,000
Santa Rosa Q1 2003 COND 3 $292,577.59 $375,000
Santa Rosa Q1 2003 COND 4 $420,000.00 $375,000
Santa Rosa Q1 2004 COND 1 $184,909.09 $425,000
Santa Rosa Q1 2004 COND 2 $272,024.92 $425,000
Santa Rosa Q1 2004 COND 3 $333,270.00 $425,000
Santa Rosa Q1 2004 COND 4 $277,000.00 $425,000

TIA
 
Hi,

I think this will work (Air Code):
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms)),'$#,###') AS [Median Price]

Assumptions:
1) acbDMedian takes the same kind of arguments as the VBA 'DAvg' function.
2) Sales2.Bedrooms is a numeric field.

Hope this helps,
Alex.
 
Hi again,

Thinking about it, you'll also need to include the 'Santa Rosa' and 'Cond'
conditions in the acbDMedian call, or else you'll get the median across all
cities and property types.

Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

This should help more :)

Alex.




Tokyo Alex said:
Hi,

I think this will work (Air Code):
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms)),'$#,###') AS [Median Price]

Assumptions:
1) acbDMedian takes the same kind of arguments as the VBA 'DAvg' function.
2) Sales2.Bedrooms is a numeric field.

Hope this helps,
Alex.


rdcsfd said:
I'm using the following query to get quarterly results for each bedroom
number. The median function is returning the same number for each bedroom
number in each quarter. Can someone tell me how to get it to provide the
median price by bedroom number?

SELECT DISTINCTROW Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy')
AS [Quarter], Sales2.[Property Subtype 1], Sales2.Bedrooms AS [Bedrooms],
Avg(Sales2.[Selling Price]) AS [Average Price],
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') &
"'")),'$#,###') AS [Median Price], Min(Sales2.[Selling Price]) AS [Lowest
Price], Max(Sales2.[Selling Price]) AS [Highest Price], Count(*) AS [# Sold]
FROM Sales2
WHERE Sales2.City = 'Santa Rosa' AND Sales2.[Property Subtype 1] = 'Cond'
GROUP BY Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy'),
Sales2.[Property Subtype 1], Sales2.Bedrooms, Year([Sales2].[Selling
Date])*4+DatePart('q',[Sales2].[Selling Date])-1;

Here are the results from the above query:
City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $375,000
Santa Rosa Q1 2003 COND 2 $238,704.17 $375,000
Santa Rosa Q1 2003 COND 3 $292,577.59 $375,000
Santa Rosa Q1 2003 COND 4 $420,000.00 $375,000
Santa Rosa Q1 2004 COND 1 $184,909.09 $425,000
Santa Rosa Q1 2004 COND 2 $272,024.92 $425,000
Santa Rosa Q1 2004 COND 3 $333,270.00 $425,000
Santa Rosa Q1 2004 COND 4 $277,000.00 $425,000

TIA
Click to expand...
Click to expand...
 
Oops, forgot an 'AND' before the new criteria.

3rd time round:
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" AND Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

Alex.


Tokyo Alex said:
Hi again,

Thinking about it, you'll also need to include the 'Santa Rosa' and 'Cond'
conditions in the acbDMedian call, or else you'll get the median across all
cities and property types.

Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

This should help more :)

Alex.




Tokyo Alex said:
Hi,

I think this will work (Air Code):
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms)),'$#,###') AS [Median Price]

Assumptions:
1) acbDMedian takes the same kind of arguments as the VBA 'DAvg' function.
2) Sales2.Bedrooms is a numeric field.

Hope this helps,
Alex.


rdcsfd said:
I'm using the following query to get quarterly results for each bedroom
number. The median function is returning the same number for each bedroom
number in each quarter. Can someone tell me how to get it to provide the
median price by bedroom number?

SELECT DISTINCTROW Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy')
AS [Quarter], Sales2.[Property Subtype 1], Sales2.Bedrooms AS [Bedrooms],
Avg(Sales2.[Selling Price]) AS [Average Price],
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') &
"'")),'$#,###') AS [Median Price], Min(Sales2.[Selling Price]) AS [Lowest
Price], Max(Sales2.[Selling Price]) AS [Highest Price], Count(*) AS [# Sold]
FROM Sales2
WHERE Sales2.City = 'Santa Rosa' AND Sales2.[Property Subtype 1] = 'Cond'
GROUP BY Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy'),
Sales2.[Property Subtype 1], Sales2.Bedrooms, Year([Sales2].[Selling
Date])*4+DatePart('q',[Sales2].[Selling Date])-1;

Here are the results from the above query:
City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $375,000
Santa Rosa Q1 2003 COND 2 $238,704.17 $375,000
Santa Rosa Q1 2003 COND 3 $292,577.59 $375,000
Santa Rosa Q1 2003 COND 4 $420,000.00 $375,000
Santa Rosa Q1 2004 COND 1 $184,909.09 $425,000
Santa Rosa Q1 2004 COND 2 $272,024.92 $425,000
Santa Rosa Q1 2004 COND 3 $333,270.00 $425,000
Santa Rosa Q1 2004 COND 4 $277,000.00 $425,000

TIA
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the try, but it now gives me a spurious median price:

City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $3,061
Santa Rosa Q1 2003 COND 2 $238,704.17 $3,061
Santa Rosa Q1 2003 COND 3 $292,577.59 $3,061
Santa Rosa Q1 2003 COND 4 $420,000.00 $3,061
Santa Rosa Q1 2004 COND 1 $184,909.09 $3,061
Santa Rosa Q1 2004 COND 2 $272,024.92 $3,061
Santa Rosa Q1 2004 COND 3 $333,270.00 $3,061
Santa Rosa Q1 2004 COND 4 $277,000.00 $3,061


Tokyo Alex said:
Oops, forgot an 'AND' before the new criteria.

3rd time round:
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" AND Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

Alex.


Tokyo Alex said:
Hi again,

Thinking about it, you'll also need to include the 'Santa Rosa' and 'Cond'
conditions in the acbDMedian call, or else you'll get the median across all
cities and property types.

Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

This should help more :)

Alex.




Tokyo Alex said:
Hi,

I think this will work (Air Code):
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms)),'$#,###') AS [Median Price]

Assumptions:
1) acbDMedian takes the same kind of arguments as the VBA 'DAvg' function.
2) Sales2.Bedrooms is a numeric field.

Hope this helps,
Alex.


:

I'm using the following query to get quarterly results for each bedroom
number. The median function is returning the same number for each bedroom
number in each quarter. Can someone tell me how to get it to provide the
median price by bedroom number?

SELECT DISTINCTROW Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy')
AS [Quarter], Sales2.[Property Subtype 1], Sales2.Bedrooms AS [Bedrooms],
Avg(Sales2.[Selling Price]) AS [Average Price],
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') &
"'")),'$#,###') AS [Median Price], Min(Sales2.[Selling Price]) AS [Lowest
Price], Max(Sales2.[Selling Price]) AS [Highest Price], Count(*) AS [# Sold]
FROM Sales2
WHERE Sales2.City = 'Santa Rosa' AND Sales2.[Property Subtype 1] = 'Cond'
GROUP BY Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy'),
Sales2.[Property Subtype 1], Sales2.Bedrooms, Year([Sales2].[Selling
Date])*4+DatePart('q',[Sales2].[Selling Date])-1;

Here are the results from the above query:
City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $375,000
Santa Rosa Q1 2003 COND 2 $238,704.17 $375,000
Santa Rosa Q1 2003 COND 3 $292,577.59 $375,000
Santa Rosa Q1 2003 COND 4 $420,000.00 $375,000
Santa Rosa Q1 2004 COND 1 $184,909.09 $425,000
Santa Rosa Q1 2004 COND 2 $272,024.92 $425,000
Santa Rosa Q1 2004 COND 3 $333,270.00 $425,000
Santa Rosa Q1 2004 COND 4 $277,000.00 $425,000

TIA
Click to expand...
Click to expand...
Click to expand...
 
What happens if you replace the 'acbDMedian' with 'DAvg'? Does it give the
same result as your [Average Price] field?

Alex.


rdcsfd said:
Thanks for the try, but it now gives me a spurious median price:

City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $3,061
Santa Rosa Q1 2003 COND 2 $238,704.17 $3,061
Santa Rosa Q1 2003 COND 3 $292,577.59 $3,061
Santa Rosa Q1 2003 COND 4 $420,000.00 $3,061
Santa Rosa Q1 2004 COND 1 $184,909.09 $3,061
Santa Rosa Q1 2004 COND 2 $272,024.92 $3,061
Santa Rosa Q1 2004 COND 3 $333,270.00 $3,061
Santa Rosa Q1 2004 COND 4 $277,000.00 $3,061


Tokyo Alex said:
Oops, forgot an 'AND' before the new criteria.

3rd time round:
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" AND Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

Alex.


Tokyo Alex said:
Hi again,

Thinking about it, you'll also need to include the 'Santa Rosa' and 'Cond'
conditions in the acbDMedian call, or else you'll get the median across all
cities and property types.

Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms &" Sales2.City = 'Santa Rosa' AND
Sales2.[Property Subtype 1] = 'Cond'")),'$#,###') AS [Median Price]

This should help more :)

Alex.




:

Hi,

I think this will work (Air Code):
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') & " AND
Sales2.Bedrooms = " & Sales2.Bedrooms)),'$#,###') AS [Median Price]

Assumptions:
1) acbDMedian takes the same kind of arguments as the VBA 'DAvg' function.
2) Sales2.Bedrooms is a numeric field.

Hope this helps,
Alex.


:

I'm using the following query to get quarterly results for each bedroom
number. The median function is returning the same number for each bedroom
number in each quarter. Can someone tell me how to get it to provide the
median price by bedroom number?

SELECT DISTINCTROW Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy')
AS [Quarter], Sales2.[Property Subtype 1], Sales2.Bedrooms AS [Bedrooms],
Avg(Sales2.[Selling Price]) AS [Average Price],
Format$(CDbl(acbDMedian("[Selling Price]","Sales2","Format$([Selling
Date],'\Qq yyyy') = '" & Format$(Sales2.[Selling Date],'\Qq yyyy') &
"'")),'$#,###') AS [Median Price], Min(Sales2.[Selling Price]) AS [Lowest
Price], Max(Sales2.[Selling Price]) AS [Highest Price], Count(*) AS [# Sold]
FROM Sales2
WHERE Sales2.City = 'Santa Rosa' AND Sales2.[Property Subtype 1] = 'Cond'
GROUP BY Sales2.City, Format$([Sales2].[Selling Date],'\Qq yyyy'),
Sales2.[Property Subtype 1], Sales2.Bedrooms, Year([Sales2].[Selling
Date])*4+DatePart('q',[Sales2].[Selling Date])-1;

Here are the results from the above query:
City Quarter Type Bedrooms Average Median
Santa Rosa Q1 2003 COND 1 $163,666.67 $375,000
Santa Rosa Q1 2003 COND 2 $238,704.17 $375,000
Santa Rosa Q1 2003 COND 3 $292,577.59 $375,000
Santa Rosa Q1 2003 COND 4 $420,000.00 $375,000
Santa Rosa Q1 2004 COND 1 $184,909.09 $425,000
Santa Rosa Q1 2004 COND 2 $272,024.92 $425,000
Santa Rosa Q1 2004 COND 3 $333,270.00 $425,000
Santa Rosa Q1 2004 COND 4 $277,000.00 $425,000

TIA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top