Others

How to Use Two Foreign Keys for Employee and Evaluator in Microsoft Access

Bushra Parveen Oct 15, 2026 869 views

Question details

Design a relational database table for employee evaluations that requires linking both the evaluated employee and the supervisor to the same employee table.

How to Use Two Foreign Keys for Employee and Evaluator in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating an evaluation table where an evaluation involves one employee and one evaluator, both of whom reside in the primary employee table.
Observed behavior: The goal is to implement a direct relationship using two foreign keys referencing the same table, eliminating the need for overly complex junction tables.

Before you start

Ensure your primary employee table (e.g., tblEmployees) is fully created and has a designated Primary Key (such as EmployeeID) before configuring the relationships.

Solution 1Recommended

Configure Two Foreign Keys in the Evaluation Table

Use this approach to establish a direct relationship by adding the employee table twice in the Access relationship schema.

A single evaluation involving one employee and one supervisor does not require multiple junction tables. You can achieve this by adding two foreign keys directly to your evaluations table, which will both point back to the main employee table.

Add Foreign Key Fields

Open your evaluation table (tblEvaluations) in Design View. Add a field named 'EmployeeID' for the person being evaluated, and a second field named 'EvaluatorID' for the supervisor. Set both data types to match the primary key in tblEmployees (usually Number/Long Integer).

Open the Relationships Window

Navigate to the Database Tools tab and click on 'Relationships'. Right-click the background and select 'Show Table'.

Add the Employee Table Twice

Add tblEvaluations once, and add tblEmployees twice. Access will automatically alias the second employee table as 'tblEmployees_1'.

Establish the Relationships

Drag the primary key from tblEmployees to the 'EmployeeID' field in tblEvaluations. Then, drag the primary key from tblEmployees_1 to the 'EvaluatorID' field in tblEvaluations. Check 'Enforce Referential Integrity' for both links.

Configure Two Foreign Keys in the Evaluation Table

Relationship Design Tip: Adding the table twice in the relationships diagram does not duplicate your data; it simply models the two distinct roles (employee and evaluator) interacting with the evaluation record.

Solution 2

Restrict the Evaluator Field to Supervisors Only

Enhance data integrity by ensuring the EvaluatorID field only accepts employees who are qualified supervisors.

Free Microsoft Office alternative

Manage Evaluation Data Easily with WPS Spreadsheet

While complex relational databases like Microsoft Access require significant setup, you can efficiently track employee and evaluator data using WPS Spreadsheet. WPS Office offers a free, highly compatible alternative to Microsoft Office, complete with VLOOKUP, data validation, and pivot tables to link and analyze evaluator roles seamlessly without building complex database schemas.

1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
2. Create a Data Roster: Open WPS Spreadsheet and create a master sheet containing your employee records and their supervisor status.
3. Link Evaluator Data: Use Data Validation to create drop-down lists and VLOOKUP functions to automatically pull supervisor details into your evaluation tracker.

100% compatible with Microsoft Excel formats (.xlsx and .csv)Use built-in VLOOKUP and XLOOKUP to link employee and evaluator data instantlyFamiliar interface that requires zero learning curve for Office usersFree, lightweight suite that runs smoothly on almost any device

microsoft office alternative - wps office

Frequently Asked Questions

Do I need a junction table for an employee-evaluator relationship?

No. A junction table is only necessary if a single evaluation can involve multiple employees or multiple evaluators. For a strict one-employee to one-evaluator relationship per record, using two foreign keys in the evaluation table is the correct and simpler design.

How do I link a table to itself in Access Relationships?

You can achieve this by adding the same table to the Relationships window multiple times. Access will automatically append an underscore and a number (e.g., tblEmployees_1) to differentiate the instances, allowing you to drag foreign keys to each distinct role.

What if my supervisors are stored in a completely separate table?

If supervisors have attributes strictly unique to them, you can create a subtype table (e.g., tblSupervisors) that has a one-to-one relationship with tblEmployees. Your evaluation table would then have an EmployeeID referencing tblEmployees and an EvaluatorID referencing tblSupervisors.

Office Quick Solutions