Creating db with relationships

[superrob5]

I have a table that has user information
EmpID
FirstName
LastName
Site
Phone
Voicemail
Modem


then I have a table that will hold the cell phone list. which consit of
EmpID
CellNumber
Model
SerialNumber
Active
DateAssigned

This would be a 1 to many relationship but not sure what I should do to make it. I can only make it one to one for some reason.

 

[pcEars]

No problem setting the relationship on my machine

 

[pcEars]

depends on your needs

If EmpID has only 1 or 2 cell phones at any given time, the tables should likely be combined

If EmpID is the salesman selling many phones, probably best as is

 

[superrob5]

k I changed I realized that I need to have a primary key in the cell table. Can you look and tell me what you think I should do to improve if needed.

 

[jeremie_ingram]

The problem is the EmpID is the primary key in both tables. You need to tke the Primary Key settings off of the EmpID field in the table CellPhone. If this table will require a primary key, just add in another autonumber field.

You have it set up right except for that one point. If you try to relate 2 primary keys, it has to be a one to one relationship.

 

[Pat Hartman]

If the relationship is showing up as 1-1, you have defined EmpID as the pk for the cell phone table. Since pk's are unique, Jet determines the relationship to be 1-1. To resolve the problem, make a different field the pk of the cell table or make the pk compound by including two fields such as EmpID and CellNumber. If you want a single field pk, add an autonumber. I wouldn't use SerialNumber as the pk because different manufacturers may use the same series and cause a conflict. And I wouldn't use CellNumber or SerialNumber because your design indicates that you keep historical records and therefore, you may need to reassign the phone to a different employee and having either CellNumber or SerialNumber as the pk would prevent that.

 

[superrob5]

I am getting an errror when trying to relate a table, enforce referential integrity are violated on the many side of a relationship what does that mean exactly.

Rob

 

[Pat Hartman]

That means that you have bad data in the many-side table. IE, data that has no parent in the 1-side table. You must correct the data before Access will allow you to enforce RI.