Track when employees change depot (1 Viewer)

[smally]

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.

 

[The_Doc_Man]

This becomes an issue in parent/child tables. The only way to track employee X's history is to have an employee history table that includes whatever is relevant to history.

Like, in your case, the date joining the depot and the date leaving the depot plus the depot number and employee number would be the minimum requirement as you describe it. (You could do without the "leave date" but the query to identify training just gets harder.)

Look up normalization if you haven't already. I'm not saying you haven't, but that solution would have been obvious if you were comfortable with normalization.

 

[smally]

I do know about normalisation, but I've only ever gone up to 3NF. After that it starts to get confusing for me (or in my case more confusing).

I had a feeling I'd have to use another table table with employee id, depot id, and date moved. But because I'm still new and learning about databases and Access I made this thread with the hope that there's an easier solution out there.
And I don't know where to begin with creating queries to produce the correct results similar to the one in the attached example.

 

[smally]

I've had another go. I added a new table called tblEmpDepHistory which will store an employee id, depot id, and date to indicate when an employee moved to a depot.

I've attached the updated database with a few example records. But I can't get my qryTrainingReport to work. Can anyone help?

 

[plog]

What's 'MoveDate' mean? Coming or going? I would use 2 dates to define their time at a Depot. So instead of the ambigous 'MoveDate' I would have [StartDate] and [EndDate]. That range would define thier time at a depot and if the EndDate was null it is their current Depot--which means you can get rid of the Depot field in tblEmployees.

As to your query, can you show me what you expect it to return? Based on the data in your tables, show me what you expect to be in your queries.

 

[smally]

MoveDate is the date that the employee moved/started at the depot. I opted to not have an EndDate as I thought this would run the risk of accidentally having overlapping dates which may mean that an employee would be logged as being at 2 depots for a date range.
e.g. John Smith in Alpha depot for 1/1/12 - 2/2/13
and John Smith in Bravo depot for 1/1/13 - null

But if you say it is better to have a StartDate and EndDate then I will change it. I'm still a database amateur but I'm making every effort possible to design it the way it's supposed to be.

Attached are 2 photos of what results my query currently returns ( ) and what it should return ( )

 

[plog]

You are technically (and probably practically) right--only MoveDate is needed. However, to determine what Depot a person is at on a Date you need to ultimately have a Start/End Date. I was able to deduce that via a query.

Attached is a database that creates that query (DepotAssignment) and then another query that uses it to produce the results you are ultimately after (DepotTraining).

Let me know if you find any issues or don't understand anything.

 

[smally]

Thank you very much. I've had a good look through it and I think I understand it. I struggle to understand sub-queries but I am learning.

In your opinion, in my history table am I better off adding the EndDate field?
If so is there a way to ensure the date ranges don't/can't overlap so an employee is not assigned to 2 depots during a date.

 

[plog]

I'm not certain about end date in a table. Your current setup and my query will work, its just that with a lot of data that query could take a while to run.

If you don't have too many records or notice a performance issue with the query then I'd leave the table as you have it.

 

[smally]

I have just over 600 (and rising) employee records, thankfully an employee changing depot is not that common. And this is my first database that I've extensively used so I'm not entirely sure what constitutes as 'too many records'

I do however also want to track employee join dates and leave dates. Employee's have a habit of joining the company, leaving, only to rejoin the company again. Creating a new record for them in tblEmployees is not an option so I was considering creating a new table to hold their multiple join and leave dates.
However; with this new table to track which depot an employee has worked at, I'm now considering just using the depot history table.

This would change it to:
tblDepotHistory
ID (PK) autonumber
EmpIP (FK) number
DepID (FK) number
MoveDate date
PaymentType text (Monthly/Weekly/Contractor) - another field I need to track when it changes

The only thing that I'm concerned on is when an employee leaves the company, in the depot history table I'll need to create a new row for them, but will leave the depot field (and PaymentType) as null. And if they re-join, then another row to say which depot etc. they've 'moved' to. So the query will state they worked at a null depot between 2 dates.

Am I still best not including the EndDate?


(Sorry for being so fussy, but I will eventually be moving from Access into SQL Server so I would like to get this right)

 

[Minty]

When an employee rejoins you should create a new record for them. Their leave entitlement will reset, their probation dates, pay rates, and Payroll numbers etc etc will all be different. If you simply overwrite that information you have lost the history of it.

By completing the leaving date you no longer need to display them in any drop down lists of current employees. That would be awkward to implement if you had a separate employment start finish table linked to the single employee record.

 

[smally]

Hi Minty. Unfortunately I can't create a new record for them if they rejoin. The main goal of this database is to track what training courses the employee has done, but it also does much more. All of which are always linked to the employee. So if an employee leaves and joins a year later, then if I created a new employee record for them with their new StartDate etc., I'd have to change the employee id in all the foreign key fields so it would correctly state what that individual has done. And this would still not resolve the issue of it telling me where the employee worked at when the training course occurred.

That PaymentType field is mainly to state whether they are full-time, part-time, or an external contractor. A lot of the time we hire external contractors who eventually turn into full-time staff. Their training records are always logged, but I sometime need to make reports for how much training we've delivered for the different payment types.

 

[Minty]

Your training records would be fine - they would reference the old employee record, they wouldn't disappear. If you need to link the old to the new add a field to the employee record like "PrevEmploymentID" and you can show the history by pulling in those records. Your dept history table would still be linked by the same method.

 

[plog]

Are all employees assigned a Depot? If so, you can use the tblDepotHistory table as your employment history table. Of course for that you would need to add that [EndDate] field I previously mentioned.

If there are times a person could be employed but not at a Depot then I suggest a new table that tracks employ tenure:

Tenure
Tenure_ID, autonumber primary key
EmpID, number, foreign key to tblEmployees
Tenure_Start, date, denotes when employee started employment
Tenure_End, date, denotes when employee ended employment

That way everyone keeps their EmpID forever and you can use the Tenure table to know when they were employed.

 

[The_Doc_Man]

Unfortunately I can't create a new record for them if they rejoin. The main goal of this database is to track what training courses the employee has done, but it also does much more.

This is an example of normalization in action. You have identified that there are TWO types of data associated with the employee BUT the two types have different qualifiers and thus need to be in different tables.

Training is "permanent" in that it can cross depot assignments. It has a date, and you might indeed need to associate it with a depot assignment, but its permanence means that is merely "involved" with depots.

Depot assignments are temporary (because they have start and end dates.) They are INTIMATELY associated with depots since the assignment wouldn't exist without there being a depot to which the association could be made.

Plog's question is also relevant, since if there is such a thing as a person who is employed but currently in-transit between depots, you have found that plog's "tenure" concept becomes something to consider. That would be THREE classes of personnel-related data - the actual hire date, the "temporary" depot assignment, and the cross-assignment training history by date.

 

[smally]

You have described it perfectly. An employee's training goes with them no matter which depot they are assigned to. But these training courses are provided/paid by the depot the employee works at.
I had considered adding a foreign key depotID field in the table that stores training but felt that this added more work to the user which was also susceptible to being incorrectly entered. Or a macro to auto copy the depot, however I was wanting to somehow track employee's depot history anyway.

Most of the time I only need to list employees sorted by the depot they are currently at. But some of my queries I used count certain things, which are then categorised by the depot and the year it occured. It is here where my queries were showing incorrect results for those employee's that had moved depot.

An employee must always be assigned to a depot so I believe I can have depot history and tenure tables as one.

 

[smally]

Thanks for everyone's help. I have now received all the join dates and leave dates from our payroll system, an I've entered this into my own database.

I've used your query plog. I'm having a small issue with it. If an employee were to join our company, they sometimes supply training certificates from a course they have done themselves or from a previous job. This means the date they did this training is outside any date range that is in the tenure history table. Rightly so it should not be assigned a depot, but is there a way to have the query show the record, but leave the depot name field null.

I've attached my database with records. tblTraining shows 1576, the query shows 1557, I'd like it so the 19 records are shown in the query but with a null depot. Is this possible?

 

[plog]

You need to change all the JOINS in your query to LEFT JOINS:

Show all from tblTraining-->tblTenureHistory
Show all from tblTenureHistory-->tblEmployees
Show all from tblTenureHistory-->tblDepots

 

[smally]

I've just tried that. It hasn't worked. Still shows 1557 records.

 

[plog]

Sorry, didn't see your criteria--that's the culprit. I would make that query a subquery.

First, bring in tblTraining.ID into it then save it--no need for the LEFT JOINS as I mentioned before.

Then build another query on tblTraining and the query. Link them via the ID fields and make it a LEFT JOIN, showing all records from tblTraining. In the bottom portion, use tblTraining for all the training data instead of getting it from the query, then the other data get from the query.