checkboxes on forms that search criteria

[Guest]

Hi,

I would like to search for people on the basis of what sports they play.
For instance, lets say that I had a form with 3 checkboxes: basketball,
footbal, and baseball. I want to be able to click basketball on the form and
pull up all the records for people who play basketball. But if I selected
basketball and football, I want to find all records with either basketball
and football.

How exactly should I set up the tables for this? Should the table have a
field like "PersonID" (linked to another table with info for this peron) and
then three separate Yes/No fields for basketball, footbal, and baseball. Or,
should the table include only two fields: "PersonID" (a lookup column linked
to the "persons" table) and "SportsID" (a lookup column linked to the
"sports" table, including all three sports). If the latter is the better
method, then how would I use checkboxes with a form that enables me to search
for these people via the sports that they play?

Any assistance would be greatly appreciated!

-Jon

 

[Ernie]

For a 'normalized' database, you should have two fields
(personId, SportsId) and use a query to select which
records to display. In the criteria for your query, you
will need 8 options... select 1,2,3: 1,2: 1,3: 2,3: 1: 2:
3: or none. I did something similar with three text boxes
as my selection criteria. Lots of fun figuring out all the
possible options.

HTH