Count Query 1

[Delindan]

I'm trying to create a query in sql that will count the number of terminations (job end dates) in each month of a fiscal year starting in September. Here's what I have so far which isn't working. (I abbreviated so you wouldn't see all 12 months)

SELECT [Job Activity].[Position Number], [Job Activity].[End Date], Count([Job Activity].ID) AS month1, Count([Job Activity].ID) AS month2
FROM [Job Activity]
GROUP BY [Job Activity].[Position Number], [Job Activity].[End Date]
HAVING ((([Job Activity]![End Date])>=[txtStart] And
([Job Activity]![End Date])<DateAdd("m",1,[txtStart])
And ([Job Activity]![End Date])<DateAdd("m",2,[txtStart]) And

and so on. Any suggestions?

 

[Delindan]

So after reading and rereading the link you showed about creating a cross-tab query I realized that I was making this extra difficult for myself. (big surprise) I have a query that is giving me the results I want. What I need to do is simply put those results into a report (in my case I wrote a routine to export into an excel template) into the appropriate month. I got it working although I do have a question still.

In my routine I am using the query field YearMonth which is format([End Date],"yyyymm") to put the count results into the appropriate month. Initially, I wrote if Month(YearMonth) = 9 then put the count into the Sept cell on the spreadsheet. After a little fiddling, I realized that the Month(YearMonth) does not correspond to the real month number and I couldn't figure out why. Could you explain?


Ultimately, the way I got it to work was if right(YearMonth,2) = 9 the put it into September.

 

[PHV]

[i}I couldn't figure out why[/i]
Just because YearMonth is NOT a date ....

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Delindan]

I believe you, however why? YearMonth is a grouping from format([End Date], "yyyymm") which is a date. I'm sure this is a stupid question but I think will help me understand what's going on.

Thanks!

 

[PHV]

format([End Date], "yyyymm") is a string that has nothing to do with a date value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886