Multiple Relationships between two tables 1

[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

 

[MajP]

When you make a query click on the line joing the query and edit the join type. You will get three choices

1)only include row where the joined field from both tables are equal
(inner joint)
2)Include all records from table 1 and only those records in table 2 where the joined fields are equal
(left outer join)
3)Include all records from table 2 and only those records in table 1 where the joined fields are equal
(right outer join)

the default is 1 change it to likely 2 or possibly 3 depending on the order of tables. You will get an arrow going from right to left or left to right.

 

[glenak]

Sorry, could please explain? I'm trying to create multiple relationships between two tables, and not queries.

 

[MajP]

First I would not even bother creating a relationship, because you are unlikely to force this relationship
1)If a new employee comes aboard you probably want to be able to add them to the database even if you do not know which department. Or if a new department gets created you want to be able to create it without knowing who is going to be the manager. So you are unlikely to enforce referential integrity in any of these relations.
2) If you delete a department do you want to delete from the db all the people related to the department? If you delete an employee who is a manager do you want to delete the department? NO. So you are not going to allow cascade deletes.
3) If you are using autonumbers as your primary key or primary keys that will never change then you are not going to worry about cascade updates

So there is no purpose of even creating a relationship. The only reason would be to get the default Join type in a query. But in this case if you try to set that it causes more trouble than benefit.

The join type is always editable in any query and the relationship join type is only used to set the default join type.

1) The top image (relationship window) shows how you could create the relation and set the default join types.
2) The problem occurs when you try to create a query (query window). Middle view. See how the lines go back and forth. This query will not resolve.
3) The bottom image shows that you would have to break one of the links and add a seperate table. (So I have to edit the join anyways). The bottom image would return all
Employees, their departments if one is assigned, and that departments manager if one is assigned.