MS Access tables relationship

Question

I am using MS Access 2016 and I created tables (entities) Employees and Managers with the following attributes:

 Employees
    -EmployeeId (Primary key)
    -Name
    -LastName
    -Email
    -Phone

  Managers
    -ManagerId (Primary Key)
    -EmployeeId (Foreign Key & Unique)
    -Position

I am trying to create a relationship between the 2 entities that meet the following requirements:

1. Managers(EmployeeId) is a unique value.
2. for every record in Managers table there has to be one record in the Employees table (because the manager is an employee)

I can create a one to one relationship between the tables and I think that is fine because there can only be one ManagerId per EmployeeId (When the employee is actually a manager) but my issue is that when I add a record in my Employees tables Access is forcing me to have a record in the managers table even when I am adding a non-manager to the Employees table. Any suggestions on how I can create a relationship between the 2 tables is greatly appreciated.

Answer 1

When defining a relationship using the visual Relationships window, Access usually does a good job determining which is the primary table and which is the "related" table based on the indexes of the fields being related. But for a 1-to-1 relationship where the indexes on both fields are unique, Access uses the first table you click as the primary table and the second table--the one you dragged the first field onto--as the related table. Thus, it requires that a value be in the primary table before the related table record can be added. I assume that you defined the relationship by click and dragging in the wrong order/direction.

Delete the existing relationship between the tables. Then redefine the relationship by first clicking on the Employees.EmployeeId field, then dragging that field to the Managers.EmployeeId. The relationship window which pops up should show Table/Query: Employees on the left with Related Table/Query: Managers on the right.