Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Multiple Relationships between two tables

Multiple Relationships between two tables

Multiple Relationships between two tables

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?


RE: Multiple Relationships between two tables

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.

RE: Multiple Relationships between two tables

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

RE: Multiple Relationships between two tables

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close