Listing Records by Yes/No Categories

[AveryKrouse]

I'm creating a member database. A set of fields for each member's record are yes/no checks for membership in a musical ensemble.

I want to create a report that displays members by musical ensemble. The idea is for it to show all members who have a yes checked for ensemble A, all members for B, etc.

As is, the best I can do is a table that lists all members and shows the check boxes for each ensemble. This isn't what I want. Any suggestions?

 

[Duane Hookom]

Your issue might be your table structure. I would recommend at least three
tables:
tblMembers (one record per member)
tblEnsembles (one record per insemble/group)
tblMemberEnsemble (one record for each member in each ensemble)

This would allow you to add ensembles without adding fields. You can then
easily create a report based on the three tables that list each ensemble and
its members.

--
Duane Hookom
MS Access MVP

I'm creating a member database. A set of fields for each member's record

are yes/no checks for membership in a musical ensemble.

I want to create a report that displays members by musical ensemble. The

idea is for it to show all members who have a yes checked for ensemble A,
all members for B, etc.

As is, the best I can do is a table that lists all members and shows the

check boxes for each ensemble. This isn't what I want. Any suggestions?

 

[AveryKrouse]

(I'm very new to MS Access, as you can tell)

How would I go about doing this to achieve my goal? Can I not do this using the fields in my main database?

 

[Duane Hookom]

If you don't understand the how or why of my answer, you might want to read
up on normalizing. There are a number of good sites on the internet like
http://www.oreilly.com/catalog/accessdata3/chapter/ch04.html.

It might help to think of your members as customers, ensembles as products
they might purchase, and memberensembles as the purchase details.

--
Duane Hookom
MS Access MVP

(I'm very new to MS Access, as you can tell)

How would I go about doing this to achieve my goal? Can I not do this

using the fields in my main database?