Multiple count of query fields

[BLD21]

access2007,



I have a query that contains one table(tbl_rejects). In my query I have the fields of Company,Customer,Code,Code.

Company and Customer are group by
Code is count and the other Code is a where clause of "Reject"

This will count the reject records for each company which is used in a report.

What I would like to do is add the field "Flag" and to be able to count the "automated" records also.

This is how my query pane looks:

Company Customer Code Code Flag Flag
tbl_rejects tbl_rejects tbl_rejects tbl_rejects tbl_rejects tbl_rejects
Group By Group By Count Where Count Where
"REJECTS" "AUTO"


I need to see both the reject count and auto counts in my report.
Should I try and use a separate query for the auto's, and if so how would I use that in my report. I am already using the reject query for the control source.
Any ideas?

Thanks,

Bruce

[BLD21]

The correct spacing is not saving correctly. Here is the sql:

SELECT DISTINCTROW tbl_Rejects.COMPANY_NAME, tbl_Rejects.CUSTOMER_PROFILE_Description, Count(tbl_Rejects.SORT_DECISION_CODE) AS CountOfSORT_DECISION_CODE, Count(tbl_Rejects.AUTOMATION_FLAG) AS CountOfAUTOMATION_FLAG
FROM tbl_Rejects
WHERE (((tbl_Rejects.SORT_DECISION_CODE)="REJECT") AND ((tbl_Rejects.AUTOMATION_FLAG)="AUTOMATED"))
GROUP BY tbl_Rejects.COMPANY_NAME, tbl_Rejects.CUSTOMER_PROFILE_Description;

[orange]

Is this sql working? Is the output wrong?
I would approach it as follows:
Get the query working to give the output you want, then use report wizard on the query.

After posting I set up a small test.

See attached jpg

I changed the AND to OR so you would get the counts I think you want.