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.

[Dani]

Good question and one I haven't completely wrestled to the ground.

I think a query to pull the employees with incidents 2 yrs or older and a query to pull all incidents. Then maybe a Find Unmatched? That might be messy, but because I don't know much for code, I tend to take the long walk past Grandma's house to get to where I want to be.

As for triggering it, I've been trying to set up a form so the Main form is the New Incident and a subform shows the Existing Incidents On File.

I want a field that shows the count of Current Incidents and a message that prompts the level of discipline to assigne (eg. Third Written Warning.) (In a perfect database, this would default with the correct InfractionID and show the description "Third Written Warning.")

Perhaps I'll use the AfterUpdate when the user selects the employee from the dropdown. We don't have a ton of these a day so if the DB needs to compute a bit each time the user starts this form, I don't think that's a big issue.

Thanks again for all your help. This is only my second database so I'm still very much a newb. I really appreciate your willingness to coach.

[NeoPa]

How about looking at what is necessary to handle the task of removing unwanted records. It's not too trivial.

We start with a query that finds all records where the subsequent date for that employee is greater than two years forward of itself, and returns the [DiscDate] field only.
NB. We will need to run these queries as subqueries of the main DELETE query eventually, so these cannot be saved as separate QueryDefs (Saved Access Queries) unfortunately. I will name them and simplify the explanation by using the names though :
[QueryA]

SELECT   tD1.DiscDate
FROM     [tblDiscipline] AS [tD1]
         LEFT JOIN
         [tblDiscipline] AS [tD2]
  ON     tD1.EmployeeID=tD2.EmployeeID
 AND     tD1.DiscDate<tD2.DiscDate
WHERE    (DateAdd('yyyy',2,[tD1.DiscDate])<=Date())
  AND    ((tD1.EmployeeID=tD.EmployeeID)
   OR    (tD2.EmployeeID Is Null))
GROUP BY tD1.EmployeeID
       , tD1.DiscDate
       , tD2.EmployeeID
HAVING   (DateAdd('yyyy',2,[tD1.DiscDate])<=Min([tD2.DiscDate]))
    OR   (tD2.EmployeeID Is Null)

Now, that's a query that should (No guarantees at this stage as I have no db to work with here.) return all records for the currently processed employee (See line #10 - [tD] is a refernce from the outside query we'll come to later.) which are two years old or over, and which have no following record within that two year window. We will assume, which I believe is logical, that no employee would have two such gaps still extant within their history. If the process hadn't run at all on the table for more than two years this would be theoretically possible, but I'm hoping that will not be the case here.

Now we need a query to delete all records for an Employee where the [DiscDate] is less than or equal to the value returned by this query :

DELETE *
FROM   [tblDiscipline] AS [tD]
WHERE  ([DiscDate]<=[QueryA])

A little more straightforward, until they have to be joined together of course. It needs to be seen in context on its own first though, to understand what it's doing. You can now see where [tD] is defined.

The final SQL should be something like :

DELETE *
FROM   [tblDiscipline] AS [tD]
WHERE  ([DiscDate]<=(SELECT   tD1.DiscDate
                     FROM     [tblDiscipline] AS [tD1]
                              LEFT JOIN
                              [tblDiscipline] AS [tD2]
                       ON     tD1.EmployeeID=tD2.EmployeeID
                      AND     tD1.DiscDate<tD2.DiscDate
                     WHERE    (DateAdd('yyyy',2,[tD1.DiscDate])<=Date())
                       AND    ((tD1.EmployeeID=tD.EmployeeID)
                        OR    (tD2.EmployeeID Is Null))
                     GROUP BY tD1.EmployeeID
                            , tD1.DiscDate
                            , tD2.EmployeeID
                     HAVING   (DateAdd('yyyy',2,[tD1.DiscDate])<=Min([tD2.DiscDate]))
                         OR   (tD2.EmployeeID Is Null)))

[Dani]

You'll send me a bill? lol

I so appreciate this, NeoPa, thank you. Yes, you are correct that (in theory) no employee would have two gaps in their record. Unless I am extremely careless in loading the data when we go live ;o)

I will try this with my dummy data. Thanks again,
d

[NeoPa]

Dani:
You'll send me a bill? lol

I got part of the way through this and thought - "OMG - This is turning into a nightmare. I'd better stop now!"

Then I thought - "After all that work I've already put into this? No. That would be even more depressing."

In the end the hardest work proved to be what I'd already completed by the time I had the first thought :-) Just tidying and padding out after that point really, and putting the explanation together.

I'm happier now I have it to look back on, and I hope it works for you. Bear in mind it was done as air-work, so I had nothing to work on except what was in my head. Luckily, your original question was expressed quite well and clearly - even if there was one little contradiction that held things up for a short while.

Let us know how you get on won't you.

[Dani]

As said, I'm pretty new to Access so this causes some serious growing pains for me, but it's good to learn and stretch. I really like what Access can do and enjoy trying to solve these little puzzles myself (when they're not above my skill level.)

I've been sidetracked today with a production report form that is sketched out in Word, converted to PDF and combo boxes added. All I can think (screaming in my head) is that this would be so much more efficient if all of these reporting forms were set up and compiled in Access.

But that is a project for after this HR DB is put to bed.

I will get back to you to let you know how the suggestions above worked out.

Thanks again and Happy Friday :)

[NeoPa]

I look forward to that Dani.

PS. Fridays are nearly always happy as far as I'm concerned ;-)