Use a subquery?

[jmersing]

I would like to see a total for each month of the year in this same format, would that be a subquery? I'm not sure how to setup the syntax.

I want to see how many canid's are in each month as the months go by


SELECT Attrit.division, Count(Attrit.canid) AS CountOfcanid

FROM Attrit

where Attrit.[Hire Date]<=#2/1/2007#

GROUP BY Attrit.division

;

 

[giacomo]

I don't think you need a subquery for this, seems like you just need to include month(Attrit.[Hire Date]) into your query.

hth,
Giacomo

 

[jmersing]

I'm not sure I understand where would I put the line you entered?

 

[giacomo]

SELECT Attrit.division,month(Attrit.[Hire Date]) as month, Count(Attrit.canid) AS CountOfcanid 

FROM Attrit 

where Attrit.[Hire Date]<=#2/1/2007# 

GROUP BY Attrit.division, month(Attrit.[Hire Date]) 

;

hth,
Giacomo

 

[jmersing]

I was actually looking for a result that would populate individual columns as months, like the result of what these three queries would produce if their columns were combined.

SELECT Attrit.division, Attrit.workgroup, Count(Attrit.canid) AS Jan

FROM Attrit

WHERE Attrit.[Hire Date] <= #2/1/2007#

GROUP BY Attrit.division, Attrit.workgroup

WITH OWNERACCESS OPTION;



SELECT Attrit.division, Attrit.workgroup, Count(Attrit.canid) AS Jan

FROM Attrit

WHERE Attrit.[Hire Date] <= #3/1/2007#

GROUP BY Attrit.division, Attrit.workgroup

WITH OWNERACCESS OPTION;



SELECT Attrit.division, Attrit.workgroup, Count(Attrit.canid) AS Jan

FROM Attrit

WHERE Attrit.[Hire Date] <= #4/1/2007#

GROUP BY Attrit.division, Attrit.workgroup

WITH OWNERACCESS OPTION;

 

[giacomo]

I believe you need a crosstab query. Take a look at the help for crosstab's and post back if you're still having trouble.

hth,
Giacomo