Employee Database - Multiple records with Primary Key

[Humm_tasty]

Hi All,


I am creating a database for employee records.
Each employee has their unique ID
When an employee has a change in thier employment, (EG receives a promotion or a salary increase), I would like to be able to keep a record of this update. For example the data would read:
Jo Bloggs New Joiner 1st Jan 2018 salary of 5USD
Jo Bloggs Promotion 1st Jan 2019 SAlary of 7USD
This would cause the unique ID to have multiple entries. If i remove it as primary key then how do I maintain the data accuracy with its relationship to the rest of the employee database. If i unlink it, then how does the database know to read the most current dated entry.

I am so stuck.

Help me please!

[isladogs]

Use a separate linked table with the same primary key field and your salary info field.
This will be linked to the main table as a one to many join

You then use a query based on the two tables to view all records for an employee or all employees

[ArviLaanemets]

tblEmployees: EmployeeID, ForeName, LastName, ...
tblAppointments: AppointmentID, AppointmentCode, Appointment, ...
tblDepartments: DepartmentID, DepartmentNo, Department, ...
tblEmplAppointments: EAID, AppointmentDate, EmployeeID, AppointmentID
tblEmplDepartments: EDID, DepartmentDate, EmployeeID, DepartmentID
tblEmplSalaries: ESID, SalaryDate, EmployeeID, Salary

[orange]

Humm,

I suggest you do some research on Database Concepts and Design and Normalization.
I realize you are new here, but your post highlights you are also new to database.
Here's a link with lots of related info. I recommend you watch a few videos for background/overview,
and then work through 1 or 2 tutorials from RogersAccessLibrary identified in the linked material.
You will learn more in 30 minutes than a week/month or 2 of trial and error.

Good luck.