Query by month

[Guest]

In my current query I have three fields Date, Advisor, and then a count of
each advisor. In the Datasheet the Date shows each advisors total for a
specific date.
The Total feild is a repeat of the advisor field except I selected count
under the total row.

Example: Date Advisor Total
1/1/05 dje 5
1/1/05 mss 9
1/2/05 dje 2
1/2/05 mss 8
Etc...
Is there a way to have the data look like this
Date Advisor Total
1/05 dje 30
1/05 mss 119
2/05 dje 60
2/05 mss 89
etc
I'm pretty new to building queries other then using the wizard, so any help
would be great.

Thank you
David

 

[Michel Walsh]

Hi,


You group by month, year, and by advisor?


SELECT MONTH(theDate), YEAR(theDate), Advisor, SUM([Total])
FROM myTable
GROUP BY MONTH(theDate), YEAR(theDate), Advisor
ORDER BY YEAR(TheDate), MONTH(theDate), Advisor



the order by is optional (and just for presentation purposes, at most).

Maybe one of the important points is to see that we can GROUP on computed
expressions.

Hoping it may help,
Vanderghast, Access MVP