Selection of Yes/No data where data is "Yes"

[Guest]

I have a table that lists down a persons qualifications. That person has the
qualification "Yes" by a tick box.
I wish to run a report listing out all his/her qualifications. I have been
using "True" in the criteria portiion of the setup screen but have run out of
rows. There a large number of possible qualifications.
Any suggestions on how to do this please?

Regards

Juparo

 

[Allen Browne]

Instead of a bunch of y/n fields, you need to use a normalized design.

3 tables:
1. Person, with PersonID primary key
2. Qualification, with QualID primary key
3. PersonQual, with fields:
PersonID relates to Person.PersonID
QualID relates to Qualification.QualID

Interface:
- Main form bound to Person table
- Subform bound to PersonQual table. Continuous view.
Subform contains a combo, that has the Qualification table as RowSource.
For each qualification a person has, enter a record in the subform (one per
row.)

Report:
Create a query based on the 3 tables, and build the report on that.

Rationale:
You have a many-to-many relation between persons and qualifications. The
junction table (PersonQual) resolves that into a pair of one-to-many
relations. This is the standard solution. Another example:
http://allenbrowne.com/casu-06.html