Hi. I have a database that primarily hold training records completed on dates for employees. I have a table to store my employees with the following fields
tblEmployees
EmpID (PK) autonumber
Firstname text
Surname text
Depot (FK) number
JoinDate date
IsEmployed true/false
tblDepots
DepID number
Name text
The depot foreign key for tblEmployees is to say what depot that employee works at. Most of the time an employee will always be in the same depot during their time at the company. However there are some employees that have moved depot. Usually not a problem as I just change the depot number in the FK field.
However many of my queries/reports I use rely on dates.
If John Smith joined in Jan-2012, and while working at depot #1, completed 3 training courses.
Then in Jan-2013 John Smith moved to depot #2 and since then attends 2 more training courses.
Some of my queries are going to be wrong. The way my database works now it would state depot #2 had 3 training courses in 2012 and 2 training courses in 2013. When it needs to say depot #1 had 3 training courses in 2012.
What do I need to do for keeping track for who's at which depot, when, and how to grab this info.
I've attached an example database.
tblEmployees
EmpID (PK) autonumber
Firstname text
Surname text
Depot (FK) number
JoinDate date
IsEmployed true/false
tblDepots
DepID number
Name text
The depot foreign key for tblEmployees is to say what depot that employee works at. Most of the time an employee will always be in the same depot during their time at the company. However there are some employees that have moved depot. Usually not a problem as I just change the depot number in the FK field.
However many of my queries/reports I use rely on dates.
If John Smith joined in Jan-2012, and while working at depot #1, completed 3 training courses.
Then in Jan-2013 John Smith moved to depot #2 and since then attends 2 more training courses.
Some of my queries are going to be wrong. The way my database works now it would state depot #2 had 3 training courses in 2012 and 2 training courses in 2013. When it needs to say depot #1 had 3 training courses in 2012.
What do I need to do for keeping track for who's at which depot, when, and how to grab this info.
I've attached an example database.