Table Design Question

[J W Crosby]

I'm developing a database for a non-profit. Each person
can have one or more of several "interest codes," such
as "donor," "volunteer," "board member," "responded to
appeal xyz," etc. One person may be a donor and a
volunteer, another a volunteer who also responded to
appeal xyz, yet another could be a board
member/donor/volunteer. Over time, we add interest codes.

How can I set up a table(s) such that any person can have
an unlimited number of interest codes, and I'd be able to
(easily) select all the possible combinations in a query
for a report? Example: I want to send a letter to all the
volunteers who responded to appeal xyz.

If I need to limit myself to a set maximum number of
interest codes, I could live with it. I suspect 10-12
might suffice.

 

[mark]

You would want to set up your "interest codes"
and "appeal" info as separate tables with a one-to-many
relationship to your "person" table. Join the tables with
a person ID. That way, a person can have unlimited
interests and respond to unlimited appeals.
Your query would look something like this:

Select blah, blah, blah
From tableInterest, tablePerson
Where tableInterest.personID = tablePerson.personID

Hope that helps!

 

[John Vinson]

How can I set up a table(s) such that any person can have
an unlimited number of interest codes, and I'd be able to
(easily) select all the possible combinations in a query
for a report? Example: I want to send a letter to all the
volunteers who responded to appeal xyz.

This is a typical "Many to Many" relationship. You need three tables:

Members
MemberID
LastName
FirstName
<other membership data>

InterestCodes
InterestCode
Description Text
<any other info about this code, if any>

MemberInterests
MemberID <<< link to Members
InterestCode <<< link to InterestCodes
Comments

You'ld typically have a Subform on your member form bound to the
MemberInterest table; for each of an (unlimited) number of interests
you could just add a new record via the Subform.

 

[Mike Sherrill]

I'm developing a database for a non-profit. Each person
can have one or more of several "interest codes," such
as "donor," "volunteer," "board member," "responded to
appeal xyz," etc. One person may be a donor and a
volunteer, another a volunteer who also responded to
appeal xyz, yet another could be a board
member/donor/volunteer. Over time, we add interest codes.

I think you're on the wrong track.

"Donor" usually expresses a relationship between two parties--a
relationship in which money, goods, or services have changed hands.
Think about what it means to record "donor" as an "interest code" for
someone who doesn't give you any money, goods, or services.