Relationships building between different tables

[Relationship between tables question]

This is my first time using this discussion forum.

Basically, I have several different tables to represent different groups.
The problem I have is that I have some contact names that belongs to
different groups.

I would like to be able to do the following if it is possible to create
Relationships between these contacts in the different groups:

1. be able to change/update Contactperson A in Group 1 then it will
automatically change/update that same Contactperson A that also belong in
Group 4 & 5.

2. be able to do a mail merge for all the groups but only have
contactpersonA printed/listed once (1x) ?

One thing that I need to mention - I can not create relationship base on
last names or company's only because I have a lot of people with the same
last names and some with both last and first name - I also have different
people listed with the same company.

Please help

 

[Arvin Meyer [MVP]]

The problem you are experiencing is because you have design a flat file
database (like a spreadsheet) instead of a relational one

In a relational database, the Primary Key from one, in this case
tblContacts, should be a Foreign Key field in the other table. A query shows
the relationship. Updating the Contact information will show the correct
data for that person in no matter which group he/she is a member of.

Have a look at Crystal Long's tutorial at:

http://www.accessmvp.com/Strive4Peace/Index.htm

Look at the first 4 chapters.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Relationship between tables question" <Relationship between tables question
@discussions.microsoft.com> wrote in message
news:[email protected]...

 

[John W. Vinson]

This is my first time using this discussion forum.

Basically, I have several different tables to represent different groups.
The problem I have is that I have some contact names that belongs to
different groups.

I would like to be able to do the following if it is possible to create
Relationships between these contacts in the different groups:

1. be able to change/update Contactperson A in Group 1 then it will
automatically change/update that same Contactperson A that also belong in
Group 4 & 5.

2. be able to do a mail merge for all the groups but only have
contactpersonA printed/listed once (1x) ?

One thing that I need to mention - I can not create relationship base on
last names or company's only because I have a lot of people with the same
last names and some with both last and first name - I also have different
people listed with the same company.

Please help

Sorry... but you're way off track here, in a couple of ways!

If you have several identically structured tables, with people's names, in
different groups then you're storing data in tablenames (the group). That's
not correct design.

You're right that you cannot link by people's or companies' names: a primary
key (linking field) should be unique, stable and short, and names fail on all
three counts.

Instead of a table per group, you should consider modeling this as a typical
"many to many" relationship, with a table of People, a table of Groups, and a
table of GroupMemberships:

People
PersonID <Primary Key, maybe an Autonumber, maybe some other unique
identifier, either a Number or a shortish text person ID>
LastName
FirstName
MiddleName
<other biographical data>

Groups
GroupID <your Group 4 might be 4 here>
GroupName
<other information about the group>

GroupMembership
PersonID <link to People, who's in the group>
GroupID <link to Groups, what group she's in>
<other fields concerning this person's membership in this group, e.g. date
joined, ending date of membership, the person's role in the group, etc.>

This design will let each person be a member of zero, one, or many groups,
just by adding additional records to the GroupMembership table. You can create
a Query joining all three tables, and put a criterion on the GroupName (say)
to find all people in a group; this query can be used as the source of a
mailmerge from Word, or the recordsource of a Report, or the like.

 

[Ken Sheridan]

How you handle this depends on what data you want to store about contacts in
the different groups. If you want to store the same types of data
('attributes' in the jargon) about everybody but simply assign each contact
to one or more groups, then do as John describes.

An alternative possible scenario is that you want to store not only the
attributes common to everybody, e.g. first name, last name etc, but also
attributes which differ for each group. This would be the case if each group
represents a sub-type of contacts, e.g. one group might be directors, about
whom you'd want to store data relevant only to Directors, another might be
lawyers about whom you'd want to store data relevant only to lawyers. In
this case you'd have a table Contacts with a primary key ContactID; this
table would have columns for FirstName, LastName and all other attributes
common to everybody. You'd also have tables Directors and Lawyers. The
primary keys of these tables would again be ContactID, but in each case it
would also be a foreign key referencing the primary key of Contacts. The
Lawyers table would have other columns for those attributes relevant only to
lawyers and the Directors table would have columns for those attributes
relevant only to Directors.

Note that if the primary key ContactID of Contacts is an autonumber, the
primary keys of Lawyers and Directors must not be autonumbers, but of
straightforward long integer number data type.

Whichever of the alternative models is appropriate in your case only you can
decide, but hopefully we've given you enough to enable you to do so.

Whichever solution is appropriate in your case then the main Contacts table
would contain a CompanyID column or similar referencing the primary key of a
Companies table, assuming each contact is a contact for one company only. If
a contact can be a contact for more than one company, e.g. a lawyer
representing several companies, then you'd need another table CompanyContacts
with columns CompanyID and ContactID. This would model the many-to-many
relationship between Contacts and Companies in the same way that John's
GroupMembership table models the many-to-many relationship between Groups and
People.

Whatever solution you opt for a mail merge for all contacts would be based
on the Contacts table of course, which has one row per contact, so there'd be
no duplication.

Ken Sheridan
Stafford, England