How do I create a report based on Yes/No fields?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I maintain an Access 2000 database for a 450-member international
organization of musicians. For each record, there are about 30 Yes/No fields
indicating each member's areas of professional interest or expertise
(Composer, Organist, Singer, etc.).

I want to create a report based on the professional categories in
alphabetical order. Under each category, the name of every member who has a
Yes value for it would appear in alpha order by last name--I have set up
separate fields for first and last name, of course.

Can anyone help? I'm sure there's a simple answer, but I'm drawing a blank.
Thanks!
 
Your issue is that your table is not normalized. What happens when the
organization wants to start tracking another interest? You have to modify
tables, forms, queries, reports, and possibly code.

A better structure stores member information in the member table and
interests in a member interest table with fields like:

tblInterests (one record per type of interest)
===========
intIntID autonumber primary key
intInterest one record for each of "Composer", "Organist", "Singer", ...

tblMemberInterests
================
mbiMbIID autonumber primary key
mbiMemberID links to the primary key of your member table
mbiIntID links to the primary key of tblInterests

If you want to add "Lip Synch" as an interest, you add the record to
tblInterests.

Is it possible to change your tables? If not, you can use a union query to
create a recordset that looks much like tblMemberInterests. You can then
base your report primarily on tblMemberInterests.
 
Thank you for an invaluable suggestion. I think I can make the necessary
changes as you suggest.

Bruce
 
Back
Top