Advice please

[Tom]

What is the best way to do this

We have a list of contacts in 1 table. Another table contains a list of
[currently] 21 different committee's. Each contact may be a member of 1 or
more committee's or of none at all.

We need to be able to:

1. Search each committe for its contacts
2. Search which contact is a member of which committe

It is likely that committee name will be changed during the project, new
committee's added or some disbanded

Using Access 2002

Tom

 

[Kurt Wimberger via AccessMonster.com]

Tom:

What you want is a junction, or join, table that records only what person
is on what committee. Try:

Table name: PeopleCommittees

Structure:
Field Name Field Type
pkPCID Auto number
fkPersonID Number (or however you store the pk of each person/contact)
fkCommitteeID Number (or however you store the pk of each committee)

Now if Sally Jones (contact number 17 in the Contacts table) joins 3
committees (#'s 5, 7, 8) your data for Sally looks like:

pkPCID fkpersonID flCommitteeID
---------------------------------------
1 17 5
2 17 7
3 17 8

HTH

 

[Guest]

I would ...

.... Create a tblContacts table with a ContactID primary key and any other
information you want for each contact.
.... Create a tblCommittees table with a CommitteeID primary key and any
other information you want for each committee.
.... Create a tblMemberships table with the ContactID for each contact who is
a member of a committee and it's CommitteeID. Each contact can have multiple
records (one for each committee they are a member of).
.... Create a relationship between ContactID fields in the tblContacts and
tblMemberships tables.
.... Create a relationship between Committee fields in the tblCommittees and
tblMemberships tables.

You can then query tblMemberships for all the members of specific committees
or all the committees a contact is a member of.

Hope this helps!

Howard

What is the best way to do this

We have a list of contacts in 1 table. Another table contains a list of
[currently] 21 different committee's. Each contact may be a member of 1 or
more committee's or of none at all.

We need to be able to:

1. Search each committe for its contacts
2. Search which contact is a member of which committe

It is likely that committee name will be changed during the project, new
committee's added or some disbanded

Using Access 2002

Tom

 

[Tom]

Thanks Kurt & Howard for your advice - wiil work along those lines

Tom

I would ...

... Create a tblContacts table with a ContactID primary key and any other
information you want for each contact.
... Create a tblCommittees table with a CommitteeID primary key and any
other information you want for each committee.
... Create a tblMemberships table with the ContactID for each contact who
is
a member of a committee and it's CommitteeID. Each contact can have
multiple
records (one for each committee they are a member of).
... Create a relationship between ContactID fields in the tblContacts and
tblMemberships tables.
... Create a relationship between Committee fields in the tblCommittees
and
tblMemberships tables.

You can then query tblMemberships for all the members of specific
committees
or all the committees a contact is a member of.

Hope this helps!

Howard

What is the best way to do this

We have a list of contacts in 1 table. Another table contains a list of
[currently] 21 different committee's. Each contact may be a member of 1
or
more committee's or of none at all.

We need to be able to:

1. Search each committe for its contacts
2. Search which contact is a member of which committe

It is likely that committee name will be changed during the project, new
committee's added or some disbanded

Using Access 2002

Tom