Hi Gabby -- I have a db where there are contacts (just people) who belong to
many different "groups". Some of these people are students, some are
members, some are donors,
some contacts are in _all_ of these groups, some contacts are in _none_ of
these groups. In order
to save space in the db (since not every contact is in every group -- and
some are only in Contacts), and keep
the information organized in the way I need to use it, I have four tables,
tblContacts -- basic info about every person in the db (name address, etc)
Primary Key: ContactID -- autonumber
tblStudents -- info about classes students attend, grades, etc., with
Primary
Key: StudentID -- autonumber
tblMembers -- etc. When they started membership, etc PK: Member ID--
autonumber
tblDonations --etc. What donations were made when, by whom PK: DonationID--
autonumber
Students, Members, and Donations all have a foreign key: ContactID --
number field -- to
tie them to the main tblContacts in terms of the "who is this" information.
I wanted to see at a glance which "groups" (member, student, donor) a
Contact belonged to as I looked
at the basic Contact form.
I made a separate query for each table -- query
included that "group" table and tblContact,
and had the Prime Key of the group table, ContactID from group table, and
First and Last Name from tblContact (just in case I want to use that query
to view full names of the Contacts Involved, for example, in a combo box at
a later time -- as the only thing stored in the group tables is the
ContactID number itself).
Then, I create a tiny subform from each query with only one control on
it -- a checkbox -- visible -- using the
ContactID of the query as the record source -- this shows checked if the
contact appears in
that query, and then I placed these tiny subforms on my contact main form --
linking them by Contact ID in the subform wizard. As I scroll through the
Contacts (or if I pull up their record using a search), I can now see
whether Student, Member, and/or Donor has a checked checkbox, and I can also
use the underlying queries in other searches, forms, and reports in future.
In my case, four different tables made sense, as the information stored on
membership is quite different than the info stored for donors or students
(ie. each contact can only have one membership, but may attend as a student
many times, or donate many donations -- because of this, these tables --
student and donation, are designed to reflect these "one-to-many"
relationships, but membership is designed on a "one to one" basis). If
these terms mean nothing to you, it's tutorial time, IMHO.
If, in your case, each person can only belong to one group, and no
additional info about each group is needed, you may get by with a field in
the "person" table that simply shows a number to indicate the group (1, 2,
3, 4, etc.). This group number (or name) could be in a lookup table (so
that you could easily add groups later, if necessary -- and use a combo box
on the form to select the group number/name).
Hope that helps. -- Any MVPs or others who think this approach is clumsy,
stupid, or otherwise bad, please let me know. I am a rank beginner. (Oh,
and if you think it elegant and wise, hearing that wouldn't be bad either.)
Carol
