I am trying to make a contacts telephone database.

Staff(TelephoneNumber, Name, Team)
Assistant(TelephoneNumber, AssistantNumber)

A staff member my have several assistants or people who work closely with then in the same team. So if the person I was trying to call is not available i can try an alternative; the assistant's number. The staff menber may may none or meny assistants/colegues. All staff including assistants who are staff are in the staff table.

I think that my table design is flawed. Please can you geniuses out there help me.

Thanks in advance Martin.

Try the following:

tblStaff
StID (Autonumber - Primary Key)
FName
LName
Team

tblStPhone
StID (One-to-Many tblStaff)
PhoneNumber
PhType (Home, Office, Mobile)

tblAsst
AsstID (Autonumber - Primary Key)
StID (One-to-Many tblStaff)
FName
LName

tblAsstPhone
AsstID (One-to-Many tblStaff)
PhoneNumber
PhType (Home, Office, Mobile)

This should give you what you need...

HTH

I would like to re-state the fact that a "staff member" and "assistant" are the same thing in my staff table. In my tables i was going to use the internal phone extentions as the primary key in the staff table. In my assistants table i was going to base the PK on both the telephone extensions of the staff member and those of his/hers colegues/assistants (who are also staff members in the staff table) and link the tables via the phone extension. I hope that some of this makes sense.
If you did understand my first post then could you please just clarify what you have done.


Thanks for the time and effort, Martin

I would NOT use extension as the pk to ANY table. Use an autonumber unless you already have a staffID from another system. Then use the autonumbers to link the assistants to the staff members in the relation table.

People don't ever have to actually see the StaffID, you would have combos that find people either by last name, first name, or phone. And once the staff member is found, the subform shows the assistants.