Ms-access – Grouping and counting rows by existence of matching criteria in any of several columns

ms access

Disclaimer: I've inherited a database which is probably not quite as "normal" as I would like, and I'm limited to Jet/MS Access 2010 for my db backend and interface, as well my choice of reporting software (currently Style Intelligence, which has its own data interpretation interface (supports standard SQL, etc.). I'm open to some data restructuring if necessary.

I have several tables of assessment data from which I need to count rows that "pass" or "fail" the assessments (one row = one assessment). Each row has a series of columns which contain the answers YES, NO, NA or null, and these answers comprise the assessment. A NO value in any of the answer columns constitutes a fail. I need a count of rows that contain NO in any of these columns, and then a count of the remainder (or, no "No" values in any of the answer columns. I don't need to count the number of NOs on one row, just group and count by the existence of at least 1 NO answer in any of the given columns. There are other tables which store assessment data in a similar structure, but instead use 1, 2 and 3 as the "yes, no, NA" answers, because they're storing the value from an MS Access form's option group.

Here are some examples of rows that I have, and how they would be grouped for counting:

AsmtID  Q1      Q2      Q3      Q4      Q5      Q6      Q7      Desired grouping

3       NO      YES     YES     YES             NA              FAIL
4       NO      YES     YES     YES     YES     NO      NA      FAIL
5       YES     YES     YES     YES     YES     YES     YES     PASS
6       YES     YES     YES     YES     YES     YES     NA      PASS

It's worth noting that I'm working with a very small amount of data, certainly no more than 200 rows from any one table at a time, so computational efficiency isn't a big concern for me. My best guess would be some routine which steps through each column on a row and then returns Pass or Fail based on the first NO value found. I would really appreciate any insight on how to interpret this data. Thank you in advance!

Best Answer

SELECT COUNT(a.*) as Total, "Fail" as Status
FROM MyTable as a
WHERE a.Q1 = "NO" or a.Q2 = "NO" or a.Q3 = "NO" or
    a.Q4 = "NO" or a.Q5 = "NO" or a.Q6 = "NO" or a.Q7 = "NO"
GROUP BY "Fail"
UNION ALL
SELECT COUNT(b.*) as Total, "Pass" as Status
FROM MyTable as b
WHERE b.Q1 <> "NO" and b.Q2 <> "NO" and b.Q3 <> "NO" and 
    b.Q4 <> "NO" and b.Q5 <> "NO" and b.Q6 <> "NO" and b.Q7 <> "NO";
GROUP BY "Pass"

The syntax may have some problems, but I think this will do it for you. If you have lots more columns to add, then it isn't too difficult to write them out once and then copy, paste, and replace in notepad.

You may need to wrap all the column comparisons in a NZ() to protect against null values. Example: WHERE Nz(b.Q1,"") <> "NO"

Related Question