Queries/Reports based on check boxes

[Guest]

We currently use a database to track the members of our many boards &
committees. It looks like this (the "x" represents a check box):

Name Hospital Board Asset Committee Research Board
Mary Smith x
x
John Doe x x

...etc..

We would like to generate a report that shows each person's name followed by
the names of the committees they are on. Is there a way to do this? I'm
assuming I would have to set up a Query first and then base my report on
that, but I can't figure out how to display the committee names and not the
yes/no result from the check box. Thanks!

 

[Douglas J Steele]

Your table design really isn't conducive for something like that.

What you should have is a table that has one row for each committe each
person is on, so that you'd have something like:

Name Board
Mary Smith Hospital Board
Mary Smith Research Board
John Doe Hospital Board
John Doe Asset Committee

Fortunately, it is possible to rescue your design using a union query (as
long as you don't have too many committees)

The query would look something like:

SELECT Name, "Hospital Board" AS Board
FROM MyTable
WHERE [Hospital Board] = True
UNION
SELECT Name, "Asset Committee" AS Board
FROM MyTable
WHERE [Asset Committee] = True
UNION
SELECT Name, "Research Board" AS Board
FROM MyTable
WHERE [Research Board] = True
UNION
....

Once you've got that query built, you should be able to use it as the source
for a report.