| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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" wrote: > 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" wrote: > > > 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
What happens if you replace the 'acbDMedian' with 'DAvg'? Does it give the
same result as your [Average Price] field? Alex. "rdcsfd" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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" wrote: > > > > > > > > > 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 |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Chart with Quarterly and Monthly Results | =?Utf-8?B?R3JlZyBB?= | Microsoft Excel Charting | 0 | 14th May 2007 09:24 PM |
| Formula Help to Calculate Quarterly Results | Paul Sheppard | Microsoft Excel Misc | 4 | 25th Jul 2006 06:23 AM |
| XNPV vs. NPV(quarterly) different results | =?Utf-8?B?bWFnaXM=?= | Microsoft Excel Worksheet Functions | 3 | 6th Jan 2006 05:21 PM |
| Interesting coincidence --- ATi's quarterly results and the launch of the X1xxx series. | John Lewis | ATI Video Cards | 3 | 1st Oct 2005 07:19 PM |
| Help on reporting Quarterly Results using VB Script | Jean | Microsoft VB .NET | 2 | 3rd Oct 2003 11:22 PM |
