Auto update Records

[donsi]

Hello,

Screaming for Help! right now. I am not an Access pro. Just know basics to develop small DBs and enough VB to do small things like printing a form.

Our store uses points based system to keep track of attendance for all employee. For example, if you called out, 1 point will be assigned to you, late will be 0.50 assigned to you. so to help track points I create small DB which works great as far as tracking goes. Now company introduced new policy as an incentive, where 0.50 will be deducted from the oldest point received when employee maintains perfect attendance for 90 days after last call out/lateness.

so for example

1/1/18 Emp Called out 1 Point assigned
2/1/18 Emp Late 0.50 Point assigned
3/1/18 Emp Called out 1 Point assigned
Now if Emp doesn't have any occurrences for next 90 days (until June 1st), then Emp is eligible for 0.50 point dropped from 1/1/18 occurrence. So instead of 1 point, it should be only 0.50 points for 1/1/18 after June 1st, 2018. If Emp manages to do that for next 90 days (until Sep 1st), Emp is eligible for another 0.50 drop from 1/1/18 after Sep 1st, 2018.

Is there a way to achieve this without manually adjusting points every 90 days?

I have three tables and one query
TblUsers - Supervisors
TblEmployees to hold employee info
TblAttTracker to enter each occurrence via form by users.
Qrypoints to get total points for each employee.

 

[Mark_]

Is your '90 days' a rolling 90 or calendar driven?

Either way you should be able to have a function that goes through their attendance to see who does/does not qualify, but how that is calculated changes on how your business implements its rule.

 

[donsi]

Is your '90 days' a rolling 90 or calendar driven?

Either way you should be able to have a function that goes through their attendance to see who does/does not qualify, but how that is calculated changes on how your business implements its rule.

It is rolling 90 days. We will calculate 90 days from last occurance. If there is no other occurrence during those 90 days, emp will have a 0.50 dropped.

How would this function work? Would I need to create any queries?

Thank you for responding.

 

[Galaxiom]

The point balance should not be stored but calculated from the base data in a query when required.

Incrementing and decrementing a balance is prone to error.

 

[Mark_]

For any given instance, you can use DLookup to see there is another absence within 90 days. Something like

PointReduction: IIF( DLookup( '[Employee_ID]', TblAttTracker, '([Employee_ID] = ' & Employee_ID & ' AND [DateField] Between #" & [MissedDate]+ 1 & "# AND #" & [MissedDate] + 90 & "#") IS NULL, .5, 0)

More elaborate logic is

TestDate = Date
Current_ID = ID of current employee
Until Employee_ID <> Current_ID 
   Next record
   Look up the most recent absence. 
   Number of points (running total) = points (running total)- INT( (TestDate()-AbsenceDate) / 90)
   points (running total) = points (running total) + Points for this record
   TestDate = Date of last transaction
Loop

This should also do about what you are looking for, but you may need to work out exactly how you want to count points to see if going backward or forward is required for your system.

 

[Galaxiom]

For any given instance, you can use DLookup to see there is another absence within 90 days.

Using DLookups in queries is very inefficient. Better to join the table to itself to find any other records within the time period.

I would also advise that the 90 day time period be stored in a table rather than hard coded into the query.

 

[Mark_]

Using DLookups in queries is very inefficient. Better to join the table to itself to find any other records within the time period.

I would also advise that the 90 day time period be stored in a table rather than hard coded into the query.

Correct on DLookup. Not sure though how the OP wants to handle everything.

As to the 90 days, he is looking for a rolling 90 days, not a fixed period. This means having it hard coded into a query or lookup function makes more sense than having one table entry per day just to try and run it off of a table.

 

[Galaxiom]

As to the 90 days, he is looking for a rolling 90 days, not a fixed period. This means having it hard coded into a query or lookup function makes more sense than having one table entry per day just to try and run it off of a table.

The 90 days is data. So is the half point deduction. All data belongs in tables, not code.

Otherwise any alteration in the specification such as changing to 120 days or a change to the points deducted requires the developer returning to the design and finding every place the hard coded values are used instead of the user simply editing one record.

This system can even handle setting changes over time and continue to calculate correctly in the past if a change date is included in the Settings.

There is no need to have a table entry for each day. This query selects the relevant records using a self join.

SELECT FirstRecord.ID, LaterRecords.ID
FROM
tblAttTracher AS FirstRecord
INNER JOIN
tblAttTracher AS LaterRecords
ON 
     FirstRecord.ID <> LaterRecords.ID
     AND FirstRecord.EmpID = LaterRecords.EmpID
     AND LaterRecords.DateField - FirstRecord.DateField < DLookup("Margin", "Settings")

The Dlookup should only run once in this situation.

Alternatively the margin can be supplied as a parameter. So can the value to be deducted in the full calculation.

Another alternative is to join the Settings table into the query, especially if the setting changes over time facility is to be supported.

BTW I would store the points scaled as an integer and convert them for entry and display. It makes calculations faster and more reliable.

 

[Mark_]

Galaxiom,
Ah, missed your point. Thought you were talking about having a table of "90 day periods" rather than "Put your rolling period and deduction value in a config record".

I was trying for an "Easily understood" answer first. These types of systems can become rather complex quickly and the OP admits their not very experienced.

 

[donsi]

Thank you both, Mark and Galaxiom, for taking interest and responding. I am making an attempt to decipher provided codes and the direction you are trying to point me in and follow your advice. As I stated previously, just have very basic knowledge of Access and VBA.

Right now I managed to build a query to get a list of employees who are eligible to get a 0.50 dropped by comparing TblEmployees to TblAttTracker for previous 90 days. (EmpID that is not in TblAttTracker within past 90 days and is in TblEmployees). The data provided by the query is accurate so currently I will have supervisors manually deduct 0.50 point for employees on the list.

Am I heading in the right direction?

Now trying to see make this task autonomous as we have 500+ employees.

 

[donsi]

The 90 days is data. So is the half point deduction. All data belongs in tables, not code.

Otherwise any alteration in the specification such as changing to 120 days or a change to the points deducted requires the developer returning to the design and finding every place the hard coded values are used instead of the user simply editing one record.

This system can even handle setting changes over time and continue to calculate correctly in the past if a change date is included in the Settings.

There is no need to have a table entry for each day. This query selects the relevant records using a self join.

SELECT FirstRecord.ID, LaterRecords.ID
FROM
tblAttTracher AS FirstRecord
INNER JOIN
tblAttTracher AS LaterRecords
ON 
     FirstRecord.ID <> LaterRecords.ID
     AND FirstRecord.EmpID = LaterRecords.EmpID
     AND LaterRecords.DateField - FirstRecord.DateField < DLookup("Margin", "Settings")

The Dlookup should only run once in this situation.

Alternatively the margin can be supplied as a parameter. So can the value to be deducted in the full calculation.

Another alternative is to join the Settings table into the query, especially if the setting changes over time facility is to be supported.

BTW I would store the points scaled as an integer and convert them for entry and display. It makes calculations faster and more reliable.

What would be the margin and settings?

 

[The_Doc_Man]

This is a case of Old Programmer's Rule #2 coming into play. "Access won't tell you anything you didn't tell it first, or tell it how to tell you." (In this case, the 2nd leg of that statement is functional.)

I would add one field to the record that keeps points. Call it "Credits" or whatever other name floats your boat. Whatever number of points you deduct for an absence, you add back in the Credits field. That field is ALWAYS 0 when the attendance record is created. What you would then do is when you have a situation where someone has earned the half point, you add it to the date on which that half point is earned, NOT the date you are offsetting.

A person's total points is then the sum of all points minus the sum of all credits. To determine eligibility for credits, you look for 90 "clean" days where the date is greater than the last date to have any mark at all, credit OR point.

If a person has an absence on 28 Feb and perfect attendance thereafter, then (approximately) 1 Jun that person would get a credit. Depending on corporate rules, you COULD say that on 2 Sep (again, approximately), if they had another "clean" 90 days, they could get another credit. But because you look for the most recent date with EITHER kind of mark as the basis for your 90-day-rule test, you can never accumulate more than four credits in a year, and also can't accumulate a credit for 90 days after getting a point.

How you want to handle the case of someone getting more credits than bad-boy points is up to you and your company, but the idea is that you can easily implement this test. And it is not my business to talk about a corporate rule disparagingly, but you might wish to bring up the point that, basically, WHO CARES if the person has a point or half point from a specific date? What good does it do that a credit today cancels a tardiness from two years ago? You really only care about totals, don't you? If the person has a bad habit of tardiness, you have the data you need to plot that out to show trends. If a person has a good attendance habit, you have the data you need to plot out that, too.

The ONLY reason you would care about the point's date is if you had a secondary forgiveness policy that said you don't count points older than X years. You didn't indicate that such was the case.

 

[donsi]

My apologies everyone, i forgot to mention that policy is based on rolling year as well. so today being 2/28/18, anything prior to 2/28/17 is dropped.

The policy is to take away 0.50 point from the oldest point earn in that rolling year, therefore creating the "Credit" field concept won't work (I tried that). This is because in cases employees may end up losing 1.50 points for only 1 point acquired. For example.

Let's say point is given on 3/1/18, then employee has rolling 90 days without any occurance, so on 6/2/18 employee will have 0.50 "Credit earned and again after another clean 90 days, so now total points will be 0. But then comes 3/1/19, now point is dropped since it wasn't made zero, so now you have a balance of 0 points and 1 credits. This will give total of -1 point balance. And that is the issue I am facing with creating another field or in my case another option in dropdown menu.

 

[Mark_]

Two steps back...
What is the financial impact to an employee of having "A point"?
If this is only within one year, what is the actual benefit of reducing this total as they can NEVER get more than 2 points with a rolling year?

I know its a company policy, but this is a case to examine the policy and see if it does contribute to the business. It will also give us a better idea if there are other aspects that need to be looked at.

 

[donsi]

No financial gain or loss for individual points but a tracking to reward emp with perfect attendance at the end of financial year, and identity abusers. Some may get termed.based on the points they accumulated during rolling year.
This 0.50 point drop is help emp who don’t call out often and not jeopardize their perfect attendance bonus for only 1 call out. Hope this helps

 

[Mark_]

No financial gain or loss for individual points but a tracking to reward emp with perfect attendance at the end of financial year, and identity abusers. Some may get termed.based on the points they accumulated during rolling year.
This 0.50 point drop is help emp who don’t call out often and not jeopardize their perfect attendance bonus for only 1 call out. Hope this helps

So if you call off the beginning of the fiscal year, you should earn enough points to have than not counted. If you call off a month before the END of the fiscal year, you don't see a benefit.

Is this the goal or is this a side effect? Having done human resources in the past, this type of a system benefits those who are sick for two days straight while causing those who run a few minutes late several times in a year to suffer.

The first part, tardy and absent, makes a lot of sense for tracking purposes and is rather easy to do. The "Getting points for not being off in a rolling 90 days" seems far more of a headache than what anyone would really want and does bias against those who get ill at the end of a fiscal year rather than the beginning. It also means consecutive days off are not penalized as much as having them spread out.

At the moment, I am trying to clarify what the rules for this are and if you will run into issues. Also does the rolling 90 days have any exceptions for periods, such as holiday?

 

[donsi]

Unfortunately that is the policy. I may agree or disagree to it but not in control of it. As far as rolling 90 Days goes, holidays doesn’t effect anything. It’s just 90 days.

 

[Mark_]

Galaxiom's post got me thinking...

You will need to write a small piece of code that runs when your app first starts up. What it will 1) Check a configuration record to see if the code has been run for the day yet. If yes, its done. 2) If it hasn't run, update the configuration record to show it has run for today (keeping others from doing the same) then 3) loop through all employees. 4) For each employee, look up their last entry in the attendance table. If it is greater than the number of days the configuration record says for crediting, it 5) adds a record to the attendance table showing "Credit for showing up for work" and adds the amount the configuration records says.

This means your attendance record will need to have your "points" as a decimal and you would use a configuration record to hold "value for tardy, value for late, value for showing up, Days on time, Last Date attendance ran, Period to track" to hold your values for update.

Then when someone is marked tardy it looks to the configuration record to see how many points to add, same with tardy.

When you report, you do a sum query for each employee with a date range of DATE() and DATE() - Period from config record OR user entered.

This way if something changes, unless the management wants you to go back and retroactively apply the rules they simply apply from when they change forward. Means you can have an on time period of 90 days, 30 days, or how ever many they think will matter. Also means you could eventually work out logic for "Month" instead of a fixed number of days.