Many-to-Many Relationship

[jessa_lee]

I'm building a company phone directory in Access to make updates/changes easier (since they previously spread it over 8 Excel spreadsheets *aack*).

I have a table for employees, one for phone numbers, and one for location information. Some employees have multiple phone numbers, and some phone numbers are shared by multiple employees, leaving me with a many-to-many relationship, which really doesn't work in Access.

I tried building a linking table between the two with just the PKs from Emp & Phone listed. It doesn't seem to work when I start building queries or forms to find or enter new data. For instance, I created a form from a query that lists the employee information and phone number & extension. It gives me an error when I try to enter new info in, saying I cannot enter new data into "the one side" of a relationship.

Any help or input would be greatly appreciated!
Jessa

 

[dcx693]

leaving me with a many-to-many relationship, which really doesn't work in Access

How exactly do you have your many-to-many relationship set up? I would create a third table joining table to match up employees with phone numbers. I would then join the employees table to it, then join the phone number table to it.

 

[jessa_lee]

I tried building a linking table between the two with just the PKs from Emp & Phone listed. It doesn't seem to work when I start building queries or forms to find or enter new data. For instance, I created a form from a query that lists the employee information and phone number & extension. It gives me an error when I try to enter new info in, saying I cannot enter new data into "the one side" of a relationship.

Now what?

 

[jfgambit]

The three tables that you have should be enough.

tblEmployees
EmpID (PK)
FName
LName
SSNbr

tblPhone
EmpID (Linked One-to-Many to tblEmployees)
PhnNbr
Extention
PHType (Work, Home, Cell, etc)

tblLocation
EmpID (Linked One-to-Many to tblEmployees)
Address
City
State
ZipCode


With the above, it does not matter if the employees have multiple phone numbers, or share the same numbers.

HTH

 

[dcx693]

jfgambit, I think the tables need to be set up more like this:

tblPhone
PhnNbr (PK)
PHType (Work, Home, Cell, etc)

tblEmployees
EmpID (PK)
FName
LName
SSNbr

tblWhoHasWhatPhones
PhnNbr (foreign key)
EmpID (foreign key)

I don't know what relationship the location table has to the rest of this.

 

[jfgambit]

True...either way...

The first will allow for the same number to be entered multiple times, while the second will only allow a phone number to be entered once.

Really depends how jessa wants the db set-up.

BTW...hows NY today?

 

[dcx693]

The first will allow for the same number to be entered multiple times, while the second will only allow a phone number to be entered once.

Not sure I agree totally....Using my table set up, the tblWhoHasWhatPhones table can store any combination of empID and PhnNbr.

Brooklyn is snowy white today. Very fluffy white powder, though it's icky in Manhattan. Snow has a way of turning grey and black there from all the dirt. How's the weather in Sunnyvale? Must be better. I was out there last October.

 

[jessa_lee]

Thanks for all your pointers so far, but I already have the tables set up as you suggested:

tblEmployees
-EmpID# (PK)
-Fname
-Lname
-Title
-Dept

tblPhones
-PhoneNum (PK)
-LinePurpose (fax, cell, desk, etc)
-LocationCode

tblLinking
-EmpID# (to link Employees)
-PhoneNum (to link Phones)

tblLocations
-LocationCode (PK)
-LocationAddy (this is several fields)

The problem is with the form I built. I have the Locations as the main table controlling the form. In the form I linked a subform in which I used a query to pull together the Emp & Phones info. When I pull up the location I want, I try to enter a new phone number with the employee at that number, and it gives me an error message that I cannot add information to the "one side" of a relationship. Is there some way I can make this work?

Jessa

 

[dcx693]

In the form I linked a subform in which I used a query to pull together the Emp & Phones info.

How is that query structured?

 

[jessa_lee]

Check out my database (see attached). I threw a bit of random data so you could see what I'm seeing.

 

[dcx693]

OK, I took a quick look. Let me see if I can explain coherently:

You need to understand which table is being edited in your subform (i.e., either the employee table, the phone number table, the location table, or the linking table). The way the query is set up, you are now editing the employee and phone number tables directly, but not the linking table.

None of the query output fields that feed into the subform are coming from the linking table. This is obviously a problem since you're trying to assign a phone number to a person.

OK, so fine, how do you fix this? Personally, I would set up the subform to only edit linking table information by selecting the employee from one combo box and the phone number from another combo box. You could even set up those combo boxes so that if you entered a new phone number or employee, it would confirm that you want to enter new data, and open a separate form to let you enter the details.

 

[dcx693]

You should look at Pat's sample db. His set up (if I read it correctly) lets you scroll though customers and the pick venues and vice-versa.

In your db it would be like scrolling through employees and assigning phone numbers. Your form/subform setup was a bit more complex in that you wanted to view things by location first, but you can accommodate that by having two subforms. The main form would have location, the first subform is where you could choose the employee, the second subform would allow you to choose a phone number to assign.

 

[jessa_lee]

I would set up the subform to only edit linking table information by selecting the employee from one combo box and the phone number from another combo box. You could even set up those combo boxes so that if you entered a new phone number or employee, it would confirm that you want to enter new data, and open a separate form to let you enter the details.

I can see how needing to work through the linking table rather than the employee or phone tables is necessary. I need this as user friendly for my co-workers as possible, with as little room for user error as possible. They will be constantly adding new employees and deleting terminated ones, not interchanging emps and phones (as the example db had shown).

I've tried building a subform inside a subform inside a subform for another similar project, but it just got toooooo jumbled looking with three sets of record controls along the bottom one on top of another. These combo boxes with alternate entry forms sound great. Can you point me in the right direction?

Thanks!
Jessa