Re: Multiple Values Primary Key

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alex" <Alex@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0801302E-12DC-4FC1-A4BF-BB9EB5169587@xxxxxxxxxxxxxxxx
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.

.