Filter by date and then count

[mosheva]

Hi,


I need to create a query that will return and count data from specific month.

I first create a filter by a criteria >6, that will return the month from June (and up).
Next:
On each date there are 4 categories that I need them to be counted.

When I run the query I get a count for each month times 4 categories.

I want the query to sum all of the month than return only a count for the 4 categories.

Here is what I get:

Month	status	final plan of action (categories)	count
7	Closed	PS Replacement	10
7	Closed	Upper Unit Replacement	5
7	Closed	SW Release	7
7	Closed	Lower Unit Replacement	9
8	Closed	PS Replacement	15
8	Closed	Upper Unit Replacement	3
8	Closed	SW Release	8
8	Closed	Lower Unit Replacement	7
9	Closed	PS Replacement	16
9	Closed	Upper Unit Replacement	10
9	Closed	SW Release	1
9	Closed	Lower Unit Replacement	2

And what I need:

Month	status	final plan of action (categories)	count
7+8+9	Closed	PS Replacement	41
7+8+9	Closed	Upper Unit Replacement	18
7+8+9	Closed	SW Release	16
7+8+9	Closed	Lower Unit Replacement	18

How can I make it happen?

Thanks!

[orange]

PLease post the sql of your current Query. A jpg of your tables and relationships would also help.

[mosheva]

Hן,
I didn't build it in SQL, I have built it with the Access query designer.
All the data is on the same data base so there is no relationships...

[orange]

Hmmm,
I don't have acc2010. Go to your query wizard, look at your query design, then look for a tab or control that says SQL View. Click it.

Copy and post what you find.

How many tables do you have?

[mosheva]

Hi,
There is only 1 table which hold the DB
Here is the code:

SELECT tblServiceRequest.Location, Month([Actual Open Date]) AS Grouping, tblServiceRequest.Type, tblServiceRequest.Status, tblServiceRequest.[Final Plan Of Action], Count(tblServiceRequest.[Final Resolution]) AS [CountOfFinal Resolution]
FROM tblServiceRequest
GROUP BY tblServiceRequest.Location, Month([Actual Open Date]), tblServiceRequest.Type, tblServiceRequest.Status, tblServiceRequest.[Final Plan Of Action], Month([Actual Open Date])
HAVING (((tblServiceRequest.Location)=[Select Location 1 or 2]) AND ((Month([Actual Open Date]))=[select month]) AND ((tblServiceRequest.Type)="Problem") AND ((tblServiceRequest.Status)="Closed") AND ((Month([Actual Open Date]))>6));

TNX!