How to get count of multiple criteria

[yappy77]

I have a database where all companies must meet 3 requirements in order to be in compliance. I need to know out of the total number of companies that are not in compliance how many do not meet requirement 1, how many do not meet requirement 2, and how many do not meet requirement 3. There may be variations - for example, company A meets requirement 1 but not 2 & 3; company B meets requirement 1 & 3 but not 2, etc.

These counts need to be converted into percents based on the total number of companies who are not in compliance.

How can I set this up in a query or report to show this information?

Your help is much appreciated.

Yappy

[Oralloy]

Yappy,

Use an aggregate queries to get your counts, something like:

SELECT COUNT(*)
  FROM CompanyStatistic
  WHERE (Requirement1 = 'failed');

Then, once you understand how they work, you can use some of the more "advanced" aggregate functions to fold multiple statistics into single queries.

BTW, it helps a lot if you post examples that we can help you modify to meet your requirements. Especially with SQL, as we don't know your table structure or field names.

Luck!
Oralloy

[yappy77]

Thanks for replying so quickly. I am sure your answer would solve my issue but I don't speak SQL. I only know how to set up basic queries and reports from my tables.

The table contains the following fields:
CompanyName
CWR Received - this is a Yes/No field
H&S Approved - this is a Yes/No field
Liability Approved - this is a True/False field

The company must meet all of the above requirements to be in compliance. I need to set up a query or report that will show me the total number of companies not in compliance & how many do not meet the CWR Received requirement, how many do not meet the H&S Approved requirement, and how many do not meet the Liability Approved requirement.

I hope this helps to clarify.

Thanks,
Yappy

[Oralloy]

At the risk of sounding cheeky - time to learn SQL...

Try creating a query and placing this in the "SQL View":

SELECT Count(*) AS Expr1
  FROM CompanyData
  WHERE ([CWR Received]=No)
        OR ([H&S Approved]=No)
        OR ([Liability Approved]=No);

Be sure to change the table name "CompanyDat a" to your table's name.

[yappy77]

I know I really should learn SQL but have not had the time or resources to do so.

Your SQL statement returns one number when I run the query. Maybe I wasn't specific enough in what I needed.

Let me try again:
CompanyName (need total count not in compliance)
CWR Received - this is a Yes/No field (need count of "No" out of the total number of companies above)
H&S Approved - this is a Yes/No field (need count of "No" out of the total number of companies above)
Liability Approved - this is a True/False field (need count of "No" out of the total number of companies above)

[Oralloy]

Well, if you don't have a large number of records, this works on my computer running Access....

SELECT DISTINCT
 (SELECT Count(*) FROM CompanyData WHERE ([CWR Received]=No)) AS [CWR Fail],
 (SELECT Count(*) FROM CompanyData WHERE ([H&S Approved]=No)) AS [H&S Fail],
 (SELECT Count(*) FROM CompanyData WHERE ([Liability Approved]=No)) AS [Liability Fail]
FROM CompanyData;

If you can remove the "FROM" clause, even better; however I doubt if you can.

Cheers!
Oralloy

[yappy77]

Thanks, Oralloy! This works great and we are almost there. I just need to add one more thing. I need the total count of companies that fail included with these other counts. The reason: in order to calculate a percent of the total that fail in each category.
Example:
CWR Fail Count/Total Fail Count = x%
H&S Fail Count/Total Fail Count = x%
Liability Fail Count/Total Fail Count = x%

[Oralloy]

Yappy,

Bro, you are really need to start learning SQL, if this is the sort of thing you're going to be doing on a regular basis.

Either that, or you're going to have to learn more about reporting in Access, which is an area where we have other experts who are much better than I am.

That said, we can modify your query to include another column, which simply counts companies, like so:

SELECT DISTINCT 
 (SELECT Count(*) FROM CompanyData WHERE ([CWR Received]=No)) AS [CWR Fail], 
 (SELECT Count(*) FROM CompanyData WHERE ([H&S Approved]=No)) AS [H&S Fail], 
 (SELECT Count(*) FROM CompanyData WHERE ([Liability Approved]=No)) AS [Liability Fail], 
 (SELECT Count(*) FROM CompanyData) AS [Total Companies] 
FROM CompanyData;

or so:

SELECT DISTINCT 
 (SELECT Count(*) FROM CompanyData WHERE ([CWR Received]=No)) AS [CWR Fail], 
 (SELECT Count(*) FROM CompanyData WHERE ([H&S Approved]=No)) AS [H&S Fail], 
 (SELECT Count(*) FROM CompanyData WHERE ([Liability Approved]=No)) AS [Liability Fail], 
 Count(*) AS [Total Companies] 
FROM CompanyData;

I'm not certain which will work better, performance wise. It shouldn't matter, if the table is small. However, I've seen "it shouldn't matter" go south when small tables become just a few hundred rows, because someone starts really using a feature that had previously been used trivially.

Cheers!

[yappy77]

(SELECT Count(*) FROM CompanyData) AS [Total Companies] - this gives me the total number of records in the database - 559

This answer gives me the number I need for the total number of companies that failed - 453:

SELECT Count(*) AS Expr1
FROM CompanyData
WHERE ([CWR Received]=No)
OR ([H&S Approved]=No)
OR ([Liability Approved]=No);


My numbers for the category failures are:
CWR Fail = 237
H&S Fail = 418
Liability Fail = 174

So, my percents would be as follows:
CWR Fail 237/Total # failed 453 = 52%
H&S Fail 418/Total # failed 453 = 93%
Liability Fail 174/Total # failed 453 = 38%


The previous response works fine except for needing the total # of companies that failed instead of the total # of records in the database.

Maybe there is someone else out there who can help???

Thank you!

[yappy77]

I figured out the answer to what I needed. Thank you for all your help. You have actually helped me learn a little bit about SQL.

SELECT Count(*) AS Expr1, (SELECT Count(*) FROM TestTBL2 WHERE ([CWR Sign Off]=No)) AS [CWR Fail], (SELECT Count(*) FROM TestTBL2 WHERE ([Vendor PreQualified by Health/Safety]=No)) AS [H&S Fail], (SELECT Count(*) FROM TestTBL2 WHERE ([Liability Insurance Approved]=No)) AS [Liability Fail]
FROM TestTBL2
WHERE (((TestTBL2.[CWR Sign Off])=No)) OR (((TestTBL2.[Vendor PreQualified by Health/Safety])=No)) OR (((TestTBL2.[Liability Insurance Approved])=No));

[Oralloy]

Yappy,

Glad I can help.

SQL really is very easy, once you get beyond the syntax. It's just simple set theory, if you have a decent database structure.

Good luck on your project.

Cheers!
Oralloy