Trend By Month

[Guest]

I'd like to run a query where the query grabs information current month to
date, 1 month previous, 2 months previous all the way to 5 months previous.

Example:
Units Sold June May April March Feb Jan
Red 8 6 2 5 2 5
Green 7 6 4 3 8 4
Blue 1 2 2 1 3 1

I have a column with a date field set to m/d/yyyy h:nn format. I can choose
a result between 2 dates but I do not know how to set results based on month.

 

[Guest]

Using the following:

Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),1)

provides me with results from the entire month of May. How do I now return
results for the current month June and for the month of April?

 

[Michel Walsh]

A crosstab query:


TRANSFORM COUNT(*)
SELECT unitType
FROM youKnowWhere
GROUP BY unitType
PIVOT DateDiff("m", yourDateTimeField, now()) IN(0, 1, 2, 3, 4)


the month are identified by numbers, 0 to 4, 0 being this month; 1 being
previous month, ...


Hoping it may help,
Vanderghast, Access MVP