Elegancy instead of brute force in selection of data.

[Guest]

Access 2003. Creating report based on a single table. Table consists of a
field for a name and 25 fields that are all Yes/No checkboxes. The report
must show detail on the different subsets of the data based on the number of
"Yes" choices to checkboxes. An example would be the mutually exclusive group
of those with 19 checkboxes marked "Yes". Any record will be a member of
only one subset.

Now I have been succesful using DCount and a very lengthy expression for the
selection expression, based on summing all the checkbox values in each
record, and then with lots of ANDS to help me. Surely the wise folks on this
site could add some elegance to my brute force approach. I am already aware
that I am going to be asked to do similar again in the future with more than
40 Yes/No checkboxes. I would rather not dedicate the rest of my life to
writing a single report. As always thanks for the creativty and wisdom made
available here.

 

[Allen Browne]

The root of the problem here is the fact that the data is stored
incorrectly.

Instead of one table with a name and 40 y/n fields, you need a normalized
table structure. So we have something concrete to talk about we will use
people's colors at an example.

If a person is allowed only one favorite color choice, you need two tables:
- Color table:
ColorName (Text) primary key

- Person table:
PersonID AutoNumber primary key
Surname Text
FirstName Text
ColorName Text relates to Color.ColorName

In the form, the ColorName field is a combo box, and the RowSource is the
Color table.

If the person is allowed multiple favorite colors, you need 3 tables. The
first 2 are as above, except there is no ColorName in the Person table.
Instead there is a 3rd table like this:
- PersonColor table:
PersonID foreign key to Person.PersonID
ColorName Foreign key to Color.ColorName

The interface to this table is a form bound to the Person table, with a
subform bound to the PersonColor table. You add as many rows as you need in
the subform to handle all the person's color choices, one per row, again
using the combo.

That structure makes it *very* easy to operate on the data, query, and so
on.

To make the report you ask for, use a crosstab query where the ColorName is
the Column Heading.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...