basMedian obtaining quarterly results

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
 
T

Tokyo 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.
 
T

Tokyo 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...
 
T

Tokyo Alex

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...
 
R

rdcsfd

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...
 
T

Tokyo Alex

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fiscal Year and Quarter 2
Show query results by quarter even if blank 1
Bad Query Results 6
#error message in results 8
Help with database structure! 8

Top