Question Multiple Relationship between 2 tables

[glenak]

I've been using Access for a while, and even though I consider myself quite good at it, I've become a bit rusty and I'm not really sure how to do certain things.

Anyway, here's the scenario I'm having problems with. Two tables, one "Employee", the other "Department". I want to have two relationships between these tables. The first is a one-to-one, where a department is managed by only one employee, and an employee may manage one and only one department. The second is a one-to-many, where an employee belongs to only one department and a department can have more than one employee.

The tables look like this in normalised form:

EMPLOYEE (Employee_ID, First_Name, Last_Name, Gender, DOB, Age, Job_Title, Job_Description, Department_ID*, Email, Phone_Number_VPN, Employment_Status)

DEPARTMENT (Department_ID, Department_Name, Department_Description, Room_Number, Manager_ID*)

When I create these relationships in Access, however, things don't work out the way I want them to. For one, unless an employee is managing the same department in which he belongs to, his details never come out in any query.

So, how do I solve this?

Cheers

 

[plog]

When I do X, Y happens. How do I stop Y from happening?

Wait for it.

Wait for it.

Wait for it.

Don't create those relationships in Access.

Now try and run your queries and if you still have problems post the SQL.

 

[glenak]

When I do X, Y happens. How do I stop Y from happening?

Wait for it.

Wait for it.

Wait for it.

Don't create those relationships in Access.

Now try and run your queries and if you still have problems post the SQL.

Er ... sorry, mate, but that's not really helpful. The relationship is perfectly legit. Are you saying Access is incapable of handling this sort of thing or you simply don't know, in which case we really shouldn't be having this conversation.

 

[CBrighton]

I haven't used the relationships window in 2007, but if it's the same as 2003 do you have 2 instances of the department table on there or are you connecting a single departments table to the employee table on multiple fields?

 

[glenak]

I haven't used the relationships window in 2007, but if it's the same as 2003 do you have 2 instances of the department table on there or are you connecting a single departments table to the employee table on multiple fields?

Yes, Brighton, I have two instances of the department table on there

 

[G37Sam]

Just make sure the DepManager is indexed with no duplicates and you're good

 

[glenak]

Hi, G37Sam;1092796

I did the exact same thing, but it didn't work. In fact, this is the problem I'm trying to get across to you lot.

Let me explain further. Using the relationship detailed above, suppose we have three departments: Admin, HR, R&D. And suppose we have 6 employees, three of which are John, Tracy and Jack, who are heads of Admin, HR, and R&D respectively.

The tables will contain data as thus:

For Emp
1, John, 1
2, Tracy, 2
3, Jack, 3
4, Tom, 1
5, Tammy, 2
6, Jefferson, 3

For Deps
1, Admin, 1
2, HR, 2
3, R&D, 3

When you expand the plus next to, say, HR, technically, the employees Tracy and Tammy should be listed, as they both belong to HR. But instead only Tracy gets listed. Same goes for Admin and R&D - their heads of department get listed, but every other employee that belongs to the departments don't get listed.

Why is this happening?

 

[gemma-the-husky]

the plus sign, as in the table?

we never use the plus sign - we shouldn't use tables directly anyway. use queries and forms.

anyway, what may be happening is that the +sign may be determining that the manager of the dept is what you want, rather than all the members - so you get 1 record instead of many.

ie if the table is concerned in more than one relationship, how can access choose which of the relationships to expand when you click the plus sign?

 

[glenak]

the plus sign, as in the table?

we never use the plus sign - we shouldn't use tables directly anyway. use queries and forms.

anyway, what may be happening is that the +sign may be determining that the manager of the dept is what you want, rather than all the members - so you get 1 record instead of many.

ie if the table is concerned in more than one relationship, how can access choose which of the relationships to expand when you click the plus sign?

Ok, I see what you mean. I'm very rusty in writing queries in MS Access. Also, just like you said, it seems Access is choosing to display results of the one-to-one relationship via the table view. In which case, is there a way to get it to show results of the one-to-many relationship instead?

 

[spikepl]

Check the Link Child Fields and Link Master Fields in the Property Sheet of table Deps in the design view of the table