Showing results if yes/no is null on report

[jsparker]

I have a table that has 20 yes/no fields. When creating an error report to show the null values I'd like to create a report that shows only fields that return a NO value.

I know how to set up in the query to pull only null values but when it rolls to the report I'd like to only see the fields where the value is no will the possibility of showing all 20. Is there a way to shrink the results to ONLY show the NO values and not every field and only the fields with NO values?

A, B, C, D, E, F, G, H, etc.

Report to show only A - Z that return NO value but don't I need category headings for all categories in the possibility of all 20 fields return NO value? Or is there a way to restrict on report to show only NO fields and values?

Sorry if confusing.

 

[HiTechCoach]

If your data is properly normalized this should be easy since each yes/no field would be on a separate record.

Do you have all the 20 fields in a single record? If yes, then you will need to make 20 queries, one for each check box and then a 21st one that unions all the other 20 queries together to get the data into a properly normalized state.

 

[jsparker]

Yes. All yes/no fields on one table.
So 20 queries and 21 to unionize the 20 queries?
What am I putting in the 20 queries and the 21st?

 

[HiTechCoach]

... you will need to make 20 queries, one for each check box ...

Each query will simulate a child record in the same format as if each check box was on a separate record:

Primary Key (to relate it back)
A calculated field that will have the label for he check box
the value for the check box

A union query will get all this into a single recordset.

You caoudl then crete a 22nd query using the union query as the record sourec and then only select the record that were NO.

 

[jsparker]

Each query will simulate a child record in the same format as if each check box was on a separate record:

Primary Key (to relate it back)
A calculated field that will have the label for he check box
the value for the check box

A union query will get all this into a single recordset.

You caoudl then crete a 22nd query using the union query as the record sourec and then only select the record that were NO.

Ok. So I have 20 Select Queries set up. Stated above is only Primary Key, Calculated field for check box label and value of check box. Is that the only three fields I need for each query? I don't need any other information about each line item (Name, Date, Client, etc)?

On a union query doesn't all fields have to have the same name/title? Each of my fields have a different name for the error. I apologize for my inexperience.

Update: Ok, I figured out the union query SQL with minimal issues. The query works correctly and pulls all IDs, errors resulting in -1 or 0 respectively, and the score received for the yes/no field.

Now I need to figure out how to create this next query that only pulls "No" response fields. And the query that pulls from the union query doesn't differentiate between the different errors. All I have is an ID# and while that tells me which entry it doesn't tell me WHICH field errored.

Is there a way to break them out to see which field errored? Or am I going to have to normalize the table into 20 tables for each error?

Help?

UPDATE: 7/9/09
Figured out the Union Query AS type to show categories. Then created another query from the Union query to pull only the "No" fields with errors and it shows the "type" as the category for each error.

Thanks again for your help HiTechCoach!!