Crosstab query always showing last 3 months

  • Thread starter Thread starter owenc
  • Start date Start date
O

owenc

I am trying to create a crosstab query that will only show
the last 3 months of data in the column header. What I
have listed below works to some degree but isn't the real
answer. The Where criteria is what needs to be refined.
If I take out the Where statement then I will get more
than 3 months of data in the column headers. Thanks...


TRANSFORM NZ(Count([Total]),0) AS Expr1
SELECT Region, Count(Total) AS [Total Of Total]
FROM qryExample
WHERE (((Month([MonthEnd]))=Month(Date()) Or (Month
([MonthEnd]))= Month(Date() -31) Or (Month([MonthEnd]))
=Month(Date() -62)))
GROUP BY Region
ORDER BY DateValue([MonthEnd]) DESC
PIVOT DateValue([MonthEnd]);
 
TRANSFORM NZ(Count([Total]),0) AS Expr1
SELECT Region, Count(Total) AS [Total Of Total]
FROM qryExample
GROUP BY Region
PIVOT "Mth" & DateDiff("M",[MonthEnd],Date()) IN ("Mth2","Mth1","Mth0");
 
Back
Top