query to list "no" values in report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

spence - 08 Dec 2005 03:31 GMT

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.

Reply to this Message

Duane Hookom - 08 Dec 2005 03:47 GMT

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.

Signature

Duane Hookom
MS Access MVP

>I have a simple form with field [EmployeeName] and then about a dozen
> checkboxes for questions (e.g. "Driver's License on file?"; "Social
[quoted text clipped - 15 lines]
> the
> right direction I would be most appreciative.

Reply to this Message

John Spencer - 08 Dec 2005 14:01 GMT

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"
...

Reply to this Message

spence - 08 Dec 2005 17:52 GMT

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
[quoted text clipped - 40 lines]
> > the
> > right direction I would be most appreciative.

Reply to this Message

John Spencer - 08 Dec 2005 19:14 GMT

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
[quoted text clipped - 54 lines]
>> > the
>> > right direction I would be most appreciative.

Reply to this Message

spence - 08 Dec 2005 20:19 GMT

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.
>
[quoted text clipped - 75 lines]
> >> > the
> >> > right direction I would be most appreciative.

Reply to this Message

spence - 08 Dec 2005 21:10 GMT

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.
>
[quoted text clipped - 75 lines]
> >> > the
> >> > right direction I would be most appreciative.

Reply to this Message