Grouped Counts using Criteria

[tehNellie]

Ok, I'm missing something simple here I'm sure, but I can't see the wood for the trees at the moment.

background guff
What I have is a system tracking actions being undertaken. There's an SLA for these actions which means they should be completed within 10 days. At the moment we have no reporting on whether or not we're meeting this SLA.

Now obtaining the information for all the data this year is fine and dandy and works ok. Where I'm having problems is doing a monthly breakdown for the ytd.

I'm using the following query to give me my raw data:

SELECT [Parent Table].ID, [Parent Table].[Date entered into database], 
[Parent Table].[Leave Date], [Parent Table].DateCompleted, 
DateDiff("d",[leave date],[datecompleted]) AS DaysToCompleteFromLeaving,
DateDiff("d",[date entered into database],[Datecompleted]) AS DaysToCompleteFromEntered, 
DatePart("m",[leave date]) AS [month]
FROM [Parent Table]
WHERE ((([Parent Table].[Date entered into database])>#1/1/2007#) 
AND (([Parent Table].[Leave Date])>#1/1/2007#) 
AND (([Parent Table].DateCompleted) Is Not Null));

Which gives me the various dates, the number of days it took to complete the record from when a person left the company ,from when their information was loaded into the database and finally a number for the month.

I have a query that happily gives me the average completion times on a monthly basis:

SELECT Avg(CInt([DaysToCompleteFromLeaving])) AS AvDaysFromLeaving, 
Avg(CInt([DaysToCompleteFromEntered])) AS AVDaysFromEntered,
[Completed Leaver Dates].month
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month;

but I seem to be having a great deal of difficulty specifying criteria on a Count to show me the same breakdown.

I can get a total count of records per month:

SELECT [Completed Leaver Dates].month, 
Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month;

But what I want to do is split that number into two columns, records where the completion date was >10 days and records where the completion date was <= 10 days which is where I'm having some problems.

Putting a critera in design view for the count field still returns the total number of records per month and returns the following SQL query:

SELECT [Completed Leaver Dates].month, Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
GROUP BY [Completed Leaver Dates].month
HAVING (((Count([Completed Leaver Dates].DaysToCompleteFromLeaving))>10));

I'm fairly sure it's in the HAVING clause, but I'm not sure what I'm missing.

 

[tehNellie]

Doh, adding a WHERE clause

ie

SELECT [Completed Leaver Dates].month, Count([Completed Leaver Dates].DaysToCompleteFromLeaving) AS CountOfDaysToCompleteFromLeaving
FROM [Completed Leaver Dates]
[b]WHERE [Completed Leaver Dates].DaysToCompleteFromLeaving <=10[/b]
GROUP BY [Completed Leaver Dates].month;

helps.