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?

 

[dhookom]

I would start by grouping by Format([End Date]."yyyymm") and counting whatever. If you want to get the yyyy fixed to a different year, you can use DateAdd("m"....) to add or subtract 3 or 9 months.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I tried this but didn't get the results I want. I have some test data in the table with 1 record with an End Date of 1/1/2011. The start date I entered was 9/1/2010 which should have captured right? It ran but with no results. Any ideas?

SELECT Format([End Date],"yyyymm") AS YearMonth, COUNT(*) AS [Position Number]
FROM [Job Activity]
WHERE Year([End Date]) Between [txtStart] And dateadd("m",12,[txtStart])
GROUP BY Format([End Date],"yyyymm");

 

[PHV]

Replace this:
WHERE Year([End Date])
with this:
WHERE [End Date]

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

 

[dhookom]

It looks like your criteria is attempting to compare a year number like 2010 to a date 1/1/2011 which as a number is 40544. It's like you are trying to match a mile value to feet (or meters to kilometers).

Just take out all criteria to see what you get. Then build from there a step at a time.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

Great! That worked. Now how do I get it to list by month?

 

[dhookom]

Start by

Just take out all criteria to see what you get.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

So I just have this test data in the table so there was only 1 record that matched the criteria. I added another and the months where there is data show up fine. What I'm wondering is if I can get it to show zeros for the months where there is no count?

 

[dhookom]

Do you have a table of all months? If not, create one that you can use in your query with an OUTER, LEFT, or RIGHT JOIN.

You could possibly create a crosstab where the year/month are column headings. This all depends on how you want your final data/results rendered.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I'm kind of new to this type of query. If I had a table that had the months and their definition how would a join work? Thanks!

 

[dhookom]

You would need to make sure you had queries where there was a column in each that generated a month/date in the format yyyymm. You would then build a new query with these two and join the yyyymm columns. Double click the join line and set the appropriate option in the join properties.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

I'll give it a try and let you know.

 

[Delindan]

I feel a little lame. Would like the table to have Month 1, Month 2 etc. The attrition by month query comes out like this which is appropriate for the data I have.

YearMonth Position Number
201101 1
201108 2

I started created another table called Month with two fields, month and wasn't sure what to put in the other. I need something that will link this with YearMonth in the query so it needs to be dynamic with the txtstartdate that is driving the query. I don't know how to do that. Can you help? Thanks!

 

[dhookom]

I think faq703-5466 has most of what you need.

Duane
Hook'D on Access
MS Access MVP

 

[Delindan]

This has been on the back burner for a bit so sorry for the delayed reply. I checked out the link you provided. It has the months going backward from an end date. I would like to go forward 12 months from a start date. The original query you helped me write is:

SELECT Format([End Date],"yyyymm") AS YearMonth, Count(*) AS [Position Number]
FROM [Job Activity]
WHERE ((([Job Activity].[End Date]) Between [txtStart] And DateAdd("m",12,[txtStart])))
GROUP BY Format([End Date],"yyyymm");

I would like to show all 12 months of a fiscal year starting with the txtStart. Piecing together what I've read I came up with the following which is getting a syntax error on the From line.

SELECT Format([End Date],"yyyymm") AS YearMonth, COUNT(*) AS [Position Number]
FROM [Job Activity]
WHERE ([End Date]) Between [txtStart] And dateadd("m",12,[txtStart])
GROUP BY Format([End Date],"yyyymm")
PIVOT "Mth" & Dateadd("m",1,txtStart);

Being that I've never done a crosstab much less in SQL not sure if I'm even going in the right direction. Obviously something is wrong although it was not getting a syntax error on the from line until I added the pivot line. Any suggestions? Thanks

 

[Delindan]

I made a change (although I'm still getting the syntax error on From line.

SELECT Format([End Date],"yyyymm") AS YearMonth, COUNT(*) AS [Position Number]
FROM [Job Activity]
WHERE ([End Date]) Between [txtStart] And dateadd("m",12,[txtStart])
GROUP BY Format([End Date],"yyyymm")
PIVOT "Mth" & Datediff("m", [End Date], (txtStart+12));

 

[PHV]

I'm still getting the syntax error
You can't use PIVOT without TRANSFORM ...

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

 

[Delindan]

Obviously I'm just winging it here. So sorry about that but don't know how else to learn. Anyway, looked around more at the transform and then decided to go through the wizard and see what it came up with and then modify from there. This is what I have however it's erring out I believe it's the pivot line. It doesn't like the txtStart which is a user input. It says it doesn't recognize it as a variable. What do you think?

TRANSFORM Count([Job Activity].[ID]) AS CountOfID
SELECT Format([End Date],"yyyymm") AS YearMonth, COUNT(*) AS [Position Number]
FROM [Job Activity]
WHERE ([End Date]) Between [txtStart] And dateadd("m",12,[txtStart])
GROUP BY Format([End Date],"yyyymm")
PIVOT "Mth" & DateDiff("m",[End Date],[txtStart])

 

[PHV]

I'd try something like this:
[!]PARAMETERS [txtStart] DateTime;[/!]
TRANSFORM Count([Job Activity].[ID]) AS CountOfID
SELECT Format([End Date],"yyyymm") AS YearMonth, COUNT(*) AS [Position Number]
FROM [Job Activity]
WHERE ([End Date]) Between [txtStart] And dateadd("m",12,[txtStart])
GROUP BY Format([End Date],"yyyymm")
PIVOT "Mth" & DateDiff("m",[End Date],[txtStart])

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

 

[Delindan]

That helped! Although my goal is to have each month list whether it has a count in it or not and this is still only listing the months that has counts (however with the new column heading)

YearMonth PositionNumber Mth11 Mth4
201101 1 1
201108 2 2

As you can see, it still has the original results in addition to the new month headers. I'd like 1 row or data with counts for Mth1 through Mth12