Query Multi Sets Of Data and Different DAte Periods?

[Rick4077]

Can I compare same sets of data for two different date periods???

SELECT OCDownloadRES.PROPSUBTYPE, 
Avg(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICEAvg, 
Count(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICECount
FROM OCDownloadRES
WHERE (((OCDownloadRES.CITY) Like "*") AND ((OCDownloadRES.PROPSUBTYPE) Like '*CONDO' 
Or (OCDownloadRES.PROPSUBTYPE) Like '*SFR') AND ((OCDownloadRES.PENDINGDATE) Between #7/1/2006# And #7/31/2006#)) 
OR (((OCDownloadRES.CITY) Like "*") AND ((OCDownloadRES.PROPSUBTYPE) Like '*CONDO' Or (OCDownloadRES.PROPSUBTYPE) Like '*SFR') AND ((OCDownloadRES.PENDINGDATE) 
Between #7/1/2005# And #7/26/2005#))
GROUP BY OCDownloadRES.PROPSUBTYPE;

The query runs but only one dynaset for SFR and CONDO.

Rick

 

[rac2]

Try writing an expression that gives one value for one time period and a different value for the other. Use this expression to GROUP BY as well as PROPSUBTYPE. This should give you rows for each PROPSUBTYPE and timeperiod.

SELECT IIf(OCDownloadRES.PENDINGDATE Between #7/1/2006# And #7/31/2006#, "Yr2006", "Yr2005") AS TimePeriod,
       OCDownloadRES.PROPSUBTYPE,
       Avg(OCDownloadRES.PENDINGPRICE) AS PENDINGPRICEAvg,
       Count(OCDownloadRES.PENDINGPRICE) AS  
         PENDINGPRICECount
FROM OCDownloadRES
WHERE OCDownloadRES.CITY) Like "*" 
  AND ( OCDownloadRES.PROPSUBTYPE Like '*CONDO'
        Or OCDownloadRES.PROPSUBTYPE Like '*SFR'  ) 

  AND ( OCDownloadRES.PENDINGDATE) Between #7/1/2006# And #7/31/2006# 
        OR
       OCDownloadRES.PENDINGDATE) Between #7/1/2005# And #7/26/2005#  )

GROUP BY IIf(OCDownloadRES.PENDINGDATE Between #7/1/2006# And #7/31/2006#, "Yr2006", "Yr2005"), 
         OCDownloadRES.PROPSUBTYPE;

The data ranges in the WHERE clause should match the ranges in the IIf() expression to be sure you get just the rows you wish to compare.

Although it would be possible to do the same thing with nested IIf() functions. In this approach the WHERE clause would have one all-inclusive date range.

...
IIf( date_range_1, "Range1", IIf(date_range_2, "Range2", 
"Other")  )
...
WHERE date_range_including_all_dates
...