Tricky Date Criteria

[Dani]

Hi,

I'm working on an HR database and I want to write a query that counts discipline records for the employees.

Here's the tricky part. Incidents are 'current' for two years. If an employee goes 2 yrs without an incident, their record is wiped clean. If they have an incident within the 2 yr mark, all previous records are counted no matter how far they go back.

Here are my fields:
DisciplineID
EmployeeID
ReviewTypeID (eg. Discipline or Performance)
DiscDate
DiscDescr
WarningID (eg. Safety, Attendance etc.)

Do I start by writing a query that sorts the most recent incident and weeds out people with a clean record?

Any thoughts on how to approach this would be much appreciated.

I'm not great at SQL or VBA so hoping for a simple solution in the query grid.

[NeoPa]

Hi Dani. Your explanation seems logically inconsistent - from the information posted. If the record is wiped clean when the employee remains clean for two years then any subsequent problem should only go back as far as the point where it was wiped. On the other hand, you suggest that a new offense causes all history to be shown.

I have an idea of how to set this up (with a subquery that groups by the employee and shows the Max of the date all linked in to the main table.) but the first step is always to have a very clear understanding of exactly what's required, so let's get that sorted first.

PS. I should commend you on a clear and well-worded question. Many people never manage such a succinct question :-)

[Dani]

Obviously I was still a bit muddy, but thank you :)

To clarify, if Employee has an incident in 2009 and nothing else until today, his incident in 09 would not be counted.

If Employee has an incident in 2009 and then in '10 and '11 and today, all previous incidents would be counted.

They are then issued a statement like, "As this is your fourth incident, you are now under suspension" Or "As you have no current incidents on record, you are receiving a written warning."

Basically they have to go a full two years to start clean.

I hope that clears it up. I appreciate any and all advice.

Cheers,
Dani

[Rabbit]

I believe NeoPa's question was regarding whether or not the data is purged when their "slate is wiped clean". It sound like the data is still there so their slate is conceptually wiped clean but not actually wiped clean because their infractions are still in the table.

[Dani]

Excellent comment and not something I had considered doing. My first instinct is to preserve the information, but purging would be more in line with genuinely wiping their record.

The database is still in development stages so this is an excellent discussion for me to have with our HR Manager.

Thank you for this.
Dani

[Rabbit]

I just want to say that I was not recommending that the records be deleted, there are arguments for either method. I was merely clarifying NeoPa's question.

[NeoPa]

Explaining that a little more clearly (As Rabbit says it's not about deleting data), does data prior to a two year long gap get included if a subsequent infringement/issue is logged?

Consider the following situation where Employee A has incidents on the following dates :
31/1/2005
15/3/2006
1/1/2009
31/8/2009

At the end of 2009 a report is run. Both incidents in 2009 (1/1 and 31/8) are obviously included within the list. Are the previous incidents also included?

Bear in mind that your question stated :

1. If an employee goes 2 yrs without an incident, their record is wiped clean.
2. If they have an incident within the 2 yr mark, all previous records are counted no matter how far they go back.

These statements are mutually exclusive, hence we need clarification of the request. We haven't touched on technical details yet (which would include how to extract the information required - when we know what is required).

PS. I appreciate your earlier attempt to clarify, but I'm afraid it didn't really manage to clear up the issue.

[Dani]

It's a very valid concern about the process, so I thank you for bringing it to my attention. I've now discussed it with the HR Mgr and he has instructed me to purge an employee's record once they have passed the two year mark.

This simplifies the query writing since only employees with records will be in the table.

Thanks for your help Neopa & Rabbit,
cheers,
d

[Dani]

I missed this comment as I had not refreshed my screen:

"Explaining that a little more clearly (As Rabbit says it's not about deleting data), does data prior to a two year long gap get included if a subsequent infringement/issue is logged?"

No. In this situation they would start again at first Verbal Warning.

From a privacy standpoint, it seems better practice to purge the Employee's record once they're entitled to a clean record so old infractions are not viewed if they are not relevant.

You have both been very helpful in solving this--and without forcing me to use code! Very grateful :)

[NeoPa]

Always a pleasure :-)

How do you propose to handle ensuring that such records that need to be purged are done so? How will this process be triggered?

Purging the data certainly makes the process a lot more straightforward of course. It might be possible to achieve without, but certainly not straightforward as far as I can see.