Multiple Values Primary Key

[Alex]

Hi. I'm doing a project with Access 2007, but the version probably doesn't
matter. I'm building a database for a business that keeps track of customers,
members, students, teachers, staff, etc. The issue is that someone can be
none or any combination of the above. How do I create relationships between
these different statuses? My other idea was to just do one community table,
and then provide the option for a lookup with multiple values, using status
as a separate table. Is there a solution to this problem? Please help!
Thanks dearly.

 

[Allen Browne]

Alex, your idea has merit: put all these entities in the one table.

Typically I name this table tblClient, since all the entities are clients in
one sense or another. Then you have a little look table of roles, with a
record for "Customer", one for "Member", another for "Student", and so on.

Then you have a 3rd table - tblClientRole - with fields:
ClientID relates to tblClient.ClientID
RoleID relates to tblRole.RoleID
The combination of the 2 fields is primary key in this table.
And if Joe Smith is both a "customer" and a "student", he will have 2
records in this table.

Since a "customer" could be a person or a company, I tend to use fields in
tblClient like this:
ClientID AutoNumber primary key
IsCorporate Yes/No Indicates if record is a person or
company.
MainName Text (60) Surname or company name
FirstName Text (30) Christian name for person; contact
person for Co.
...

If you also need to track groupings of people (which ones belong to a
household, which ones work for a company, etc), you might also be interested
in grabbing this example:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html