query to list "no" values in report

[spence]

I have a simple form with field [EmployeeName] and then about a dozen
checkboxes for questions (e.g. "Driver's License on file?"; "Social Security
Card on file?"; "I-9 on file?" etc.). With the exception of my EmployeeName
field, the rest of the fields in the underlying table are all yes/no fields.

What I want to generate is a report that will list each EmployeeName and
then all the fields where the value is "No" for that Employee, thus enabling
me to ensure I have all required paperwork on my employees.

I foolishly thought the query for the report would be simple but I can't for
the life of me figure out how to do it. Can I do this with a single select
query or will I need something more complex? If someone could point me in the
right direction I would be most appreciative.

 

[Duane Hookom]

I would suggest changing your table structure to a more normalized system.
Your field names are actually data values.

However, yes/no fields actually store -1 or 0. To find all the records where
none of the boxes are checked, you could add the fields and see which
records total exactly 0.
WHERE FieldA+FieldB+FieldC+FieldD = 0
This assumes there are no Null values in any of the fields.