Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Based on your sample data, can you provide what you want as the desired result? Seeing that might be easier than deciphering a word problem. –  bluefeet Aug 8 '13 at 19:21
    
I don't have the rep to answer my question right now, but a friend told me (offline) to concatenate each answer column Q1..Qn into one column in the select query, and then test that concatenated column for the existence of the string "NO", and return the appropriate value Pass or Fail (which I will group rows by) based on NO appearing or not appearing. I consider this to be a complete solution. Thanks! –  Andy B Aug 8 '13 at 19:55

1 Answer 1

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"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.