Counts & Calcuations in Select Query

[BLD21]

2007, I have a simple select query using one table and the fields below:

Company Name
Description
Decision Code

I need to show by COMPANY the Total count of DECISION CODES and of those the Total count that were "REJECT".

I would then like to take the Total Reject and / Total Number for a percentage.

Company Description Total Count Reject Count Percentage
Example: Acxiom 123acx 1,273 53 4.16%

I havn't used Access since 03 and am trying to get back in the saddle.



Thanks to all who reply!!

Bruce D.

[ajetrumpet]

have you tried anything yet??

[BLD21]

I am trying to query two counts on the same field unsuccessfully.

I created a second identical field and named it Source Code2.
In my query I have a count for Source Code Where Source Code = "REJECT" and a count for Source Code2 Where Source Code = "Like *" to try and get the total count of records in the field. But I can only manage one total or the other.

Any ideas?

Bruce

[jzwp11]

I would create two queries, one for the reject and the other for the total. Group both queries by the company (not sure whether the description field is necessary). Save both queries

Create a third query that includes both of the above queries and join them by the company field. Select the company field and the two count fields and then add a calculated field for the percentage determination.

[ajetrumpet]

I need to show by COMPANY the Total count of DECISION CODES and of those the Total count that were "REJECT".

this can be done such as:

select company, count(codes) as whatever

from ....

group by company

where codes = ''reject''

I would then like to take the Total Reject and / Total Number for a percentage.

couldn't this be done in one query, without even needing the one above??

for instance:

[CODE]

select distinct company, 

dsum("codes", "table", "[company] = '" & [company] & "'") as totalcount,

dsum("codes", "table", "[company] = '" & [company] & "' AND 

[codes] = 'reject'") as totalrejected,

([totalrejected] / [totalcount]) as percentage

from table

just an idea...