asked on

Need help with Access query

Hello,
I am write and front-end to an Access DB. Need help to write this SQL Query...

I have a table, one of the fields is called "Rating"... the Rating field value is either compliant or non-compliant

I need to write one query that will return the percentage of compliants and non-compliants...

So if I have 78 total records, what is the percent of compliants and non-compliants? IE I would want it to return 2 values... 80% (Complianet), 20%(Non-Compliant)....

Can that be done with just one query?

I know I could break it up and get the record count, then get the count of record that have compliant...or non-compliant... but I want one query to do this.

Thanks...

Answer_Me

Select (Count(1)/(Select Count(1) From table))*100 'Compliant Pct', 100 -((Count(1)/(Select Count(1) From table))*100 ) 'Non Compliant Pct'From
Table where rating ='Compliant'

Mike Eghtebas

SELECT Sum(IIf([Rating]='c',1,0)) AS c, Sum(IIf([Rating]='n',1,0)) AS n, Count([Rating]) AS T, [c]/[t] AS [c%], [c]/[t] AS [n%]
FROM Table1;

I tested above and it produces what you want. Please replace c and n with compliant, and non-compliant, respectively.

Mike

Mike Eghtebas

... [c%], [n]/[t] AS [n%]
^--- was c

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

strongd

ASKER

Damn...you guys are awesome.. that some cool SQL...

Let me see which one works the best...

Have you an answer tomorrow...

Thanks to everyone...

Markus Fischer

You don't really need to calculate NonCompliant, it's 1-Compliant. Using a different trick:

SELECT -Sum(Rating Like 'c*')/Count(*) AS Compliant, 1-Compliant AS NonCompliant
FROM <your table>

(°v°)

strongd

ASKER

Thanks everyone for your help.

I had to expect eghtebas's answer, it worked without any modification. The rest where giving me parsing errors. It might be because I am using SQL Builder in Visual Studio 2005, but when I copied and pasted eghtebas's query in SQL Builder, it worked like a charmed.

This one worked perfectly...SELECT Sum(IIf([Rating]='compliant',1,0))/Count([Rating]) AS [Compliant%], Sum(IIf([Rating]='non-compliant',1,0))/Count([Rating]) AS [Non-Compliant%]
FROM Table1;

I only had to change Table1 to the actually table name.
Thanks again.

Mike Eghtebas

Hi strongd,

Thank you for the points and the grade.

Also, when I have a question, I ask the (°v°). I wanted just acknowledge him.

Mike

Markus Fischer

Thanks Mike, but you deserved this one and you know it! -- (^v°)