query to list "no" values in report

[Guest]

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.

 

[John Spencer]

A query always contains the same number of columns, so with your current
structure you can't do easily what you want. The query below would identify
records where not all items were checked.
SELECT *
FROM TheTable
WHERE Field1 + Field2 + Field3 + Field4 + ... + Field12 <> -12

You could use a UNION query to temporarily change your data into a
normalized format and then use that to get results.
SELECT EmployeeName, Field1, "FieldOneName" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field2, "Driver's License" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field3, "SSN Card" as FieldName
WHERE Field1 = False
....

That would return data for a report in the format (0 = unchecked or false)
John Spencer : 0 : "Driver's License"
John Spencer : 0 : "SSN Card"
Robt Spencer : 0 : "I-9"
....

 

[Guest]

Thanks, John. That gives me a roadmap. I've never created a Union query but
there's no time like the present to try it. For future reference, I'm
wondering if you can suggest a better way to build a table (or tables) for
this sort of scenario? I'm not entirely sure I understand what you (and Duane
for that matter) mean by "normalization" in this context. Thanks again.

A query always contains the same number of columns, so with your current
structure you can't do easily what you want. The query below would identify
records where not all items were checked.
SELECT *
FROM TheTable
WHERE Field1 + Field2 + Field3 + Field4 + ... + Field12 <> -12

You could use a UNION query to temporarily change your data into a
normalized format and then use that to get results.
SELECT EmployeeName, Field1, "FieldOneName" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field2, "Driver's License" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field3, "SSN Card" as FieldName
WHERE Field1 = False
....

That would return data for a report in the format (0 = unchecked or false)
John Spencer : 0 : "Driver's License"
John Spencer : 0 : "SSN Card"
Robt Spencer : 0 : "I-9"
....

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.

 

[John Spencer]

Whole books are written on this concept.

Lets take your current setup.

You should have a
table Employee with fields for Employeeid, FirstName, LastName, SSN, and
Title.
another
table DocumentationRequirements with fields EmployeeID, DocumentID,
DateDocumented (or just True/False)
and probably a third table with DocumentID, DocumentName

Then you would have
--one record for each employee
--one record for each type of document
--one record for each employee in combination for each document you have for
that employee

This is a bit more complex to set up, but it does make life a lot simpler
when you have to extract the data in various formats.

Thanks, John. That gives me a roadmap. I've never created a Union query
but
there's no time like the present to try it. For future reference, I'm
wondering if you can suggest a better way to build a table (or tables) for
this sort of scenario? I'm not entirely sure I understand what you (and
Duane
for that matter) mean by "normalization" in this context. Thanks again.

A query always contains the same number of columns, so with your current
structure you can't do easily what you want. The query below would
identify
records where not all items were checked.
SELECT *
FROM TheTable
WHERE Field1 + Field2 + Field3 + Field4 + ... + Field12 <> -12

You could use a UNION query to temporarily change your data into a
normalized format and then use that to get results.
SELECT EmployeeName, Field1, "FieldOneName" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field2, "Driver's License" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field3, "SSN Card" as FieldName
WHERE Field1 = False
....

That would return data for a report in the format (0 = unchecked or
false)
John Spencer : 0 : "Driver's License"
John Spencer : 0 : "SSN Card"
Robt Spencer : 0 : "I-9"
....

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.

 

[Guest]

John,

Thanks so much. That makes perfect sense and saves me from having to read a
whole book. It's reflected in other design pieces of my db, but I'm still new
enough to this not to have the foresight in design to prevent myself from
making errors like this. I really appreciate you taking the time to educate
me.

Whole books are written on this concept.

Lets take your current setup.

You should have a
table Employee with fields for Employeeid, FirstName, LastName, SSN, and
Title.
another
table DocumentationRequirements with fields EmployeeID, DocumentID,
DateDocumented (or just True/False)
and probably a third table with DocumentID, DocumentName

Then you would have
--one record for each employee
--one record for each type of document
--one record for each employee in combination for each document you have for
that employee

This is a bit more complex to set up, but it does make life a lot simpler
when you have to extract the data in various formats.

Thanks, John. That gives me a roadmap. I've never created a Union query
but
there's no time like the present to try it. For future reference, I'm
wondering if you can suggest a better way to build a table (or tables) for
this sort of scenario? I'm not entirely sure I understand what you (and
Duane
for that matter) mean by "normalization" in this context. Thanks again.

A query always contains the same number of columns, so with your current
structure you can't do easily what you want. The query below would
identify
records where not all items were checked.
SELECT *
FROM TheTable
WHERE Field1 + Field2 + Field3 + Field4 + ... + Field12 <> -12

You could use a UNION query to temporarily change your data into a
normalized format and then use that to get results.
SELECT EmployeeName, Field1, "FieldOneName" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field2, "Driver's License" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field3, "SSN Card" as FieldName
WHERE Field1 = False
....

That would return data for a report in the format (0 = unchecked or
false)
John Spencer : 0 : "Driver's License"
John Spencer : 0 : "SSN Card"
Robt Spencer : 0 : "I-9"
....

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.

 

[Guest]

John,

Perhaps I spoke too soon. I have a complicating factor in this scenario
which is that I have four different employee types (DE, AG, IC, and GB), and
the documentation requirements for each type are different. There's
significant overlap in documentation requirements. For instance we want a
driver's license, s.s. card, W4, and I9 for all employee types, but for some
types we need copies of certifications specific to the employee type. The
employee type is recorded in a text field in tblEmployee.

Following your suggested redesign, it seems like I will need to create
separate DocumentationRequirement tables for each of the four employee types,
yes? I can't think of another way to define the differences in required
documentation for each type. If you have a better idea I'd love to hear it.

Thanks.

Whole books are written on this concept.

Lets take your current setup.

You should have a
table Employee with fields for Employeeid, FirstName, LastName, SSN, and
Title.
another
table DocumentationRequirements with fields EmployeeID, DocumentID,
DateDocumented (or just True/False)
and probably a third table with DocumentID, DocumentName

Then you would have
--one record for each employee
--one record for each type of document
--one record for each employee in combination for each document you have for
that employee

This is a bit more complex to set up, but it does make life a lot simpler
when you have to extract the data in various formats.

Thanks, John. That gives me a roadmap. I've never created a Union query
but
there's no time like the present to try it. For future reference, I'm
wondering if you can suggest a better way to build a table (or tables) for
this sort of scenario? I'm not entirely sure I understand what you (and
Duane
for that matter) mean by "normalization" in this context. Thanks again.

A query always contains the same number of columns, so with your current
structure you can't do easily what you want. The query below would
identify
records where not all items were checked.
SELECT *
FROM TheTable
WHERE Field1 + Field2 + Field3 + Field4 + ... + Field12 <> -12

You could use a UNION query to temporarily change your data into a
normalized format and then use that to get results.
SELECT EmployeeName, Field1, "FieldOneName" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field2, "Driver's License" as FieldName
WHERE Field1 = False
UNION
SELECT EmployeeName, Field3, "SSN Card" as FieldName
WHERE Field1 = False
....

That would return data for a report in the format (0 = unchecked or
false)
John Spencer : 0 : "Driver's License"
John Spencer : 0 : "SSN Card"
Robt Spencer : 0 : "I-9"
....

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.