Riddle me this you wizards

[knowshowrosegrows]

I have a parameters query that gives me perfect census data according to my
chosen date range (See 1).
Then I have a crosstab query that averages the utilization of the available
beds for that date range and shows it according to different facilities (See
2)

(1) PARAMETERS [Beginning Search Date] DateTime, [Ending Search Date]
DateTime;
SELECT tblCensusEvent.Census_ID, tblCensusEvent.Prm_Code,
tblCensusEvent.CensusDate, tblProgram.Location, tblCensusEvent.Census,
tblAgency.Agency, tblLOC.LOC_Sort, tblLOC.LOC_Alias, tblAgency.Reg,
tblCensusEvent.Admiss, tblCensusEvent.[D/C], tblProgram.Cap,
tblCensusEvent!Census/tblProgram!Cap AS Utilization
FROM (tblAgency RIGHT JOIN (tblCensusEvent LEFT JOIN tblProgram ON
tblCensusEvent.Prm_Code = tblProgram.Prm_Code) ON tblAgency.Agency_ID =
tblProgram.Agency_ID) LEFT JOIN tblLOC ON tblProgram.Prm_Code =
tblLOC.Prm_Code
WHERE (((tblCensusEvent.CensusDate) Between [Beginning Search Date] And
[Ending Search Date]));

(2) TRANSFORM Avg(qryUtilDateParameter.Utilization) AS AvgOfUtilization
SELECT Sum(qryUtilDateParameter.Cap) AS SumOfCap,
Sum(qryUtilDateParameter.Census) AS SumOfCensus,
qryUtilDateParameter.LOC_Alias
FROM qryUtilDateParameter
GROUP BY qryUtilDateParameter.LOC_Sort, qryUtilDateParameter.LOC_Alias
PIVOT qryUtilDateParameter.Agency;

What I need ultimately is a report that will give me rolling monthly
utilization data for the past 12 months. Such as this:

LOC_Alias = Detox
Agency Oct07 Nov07 Dec07(current)
ADRC 25% 19% 33%
Rushford 41% 66% 21%

LOC_Alias = Residential
Agency Oct07 Nov07 Dec07(current)
ADRC 6% 22% 34%
Rushford 37% 54% 29%

 

[KARL DEWEY]

Use this as criteria ---
WHERE (((tblCensusEvent.CensusDate) Between
DateAdd("m",-12,Date()-Day(Date()))+1 And Date()-Day(Date())));

 

[John W. Vinson]

Use this as criteria ---
WHERE (((tblCensusEvent.CensusDate) Between
DateAdd("m",-12,Date()-Day(Date()))+1 And Date()-Day(Date())));

Or

BETWEEN DateSerial(Year(Date()), Month(Date()) - 12, 1) AND
DateSerial(Year(Date()), Month(Date()), 1)


John W. Vinson [MVP]