?
Solved

Need help with Access query

Posted on 2007-10-18
9
Medium Priority
?
198 Views
Last Modified: 2010-03-20
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...
0
Comment
Question by:strongd
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:answer_me
ID: 20104385
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'
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20104841
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20104850
... [c%], [n]/[t] AS [n%]
               ^---  was c
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 20104882
or just:

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;
0
 

Author Comment

by:strongd
ID: 20105320
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...
0
 
LVL 58

Expert Comment

by:harfang
ID: 20106939
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°)
0
 

Author Comment

by:strongd
ID: 20109339
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.

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20109800
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
0
 
LVL 58

Expert Comment

by:harfang
ID: 20112569
Thanks Mike, but you deserved this one and you know it! -- (^v°)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question