Attendance with point system

[maabbas]

Hi Everyone,
Trying to figure out the query or formula which calculate the employee attendance based on point system, our company recently introduce the point system which track the employees attendance.
The attendance policy is, When an employee Absent he will get a point, and if tardy or arrive late will get 1/2 point. Employee can get a point credit if he/she has perfect attendance in 90 days of the last absent date. and if employee get another absent within the 90 days, his/her 90 days period start with last absent date and the first absent stays till next year

here is one scenario

Attandance Date	Credit Date
6/1/2014 <tbody> </tbody>	6/1/2015 get credit
07/1/2014	7/1/2015 get credit <tbody> </tbody>
08/01/2014	10/30/2014 get credit for this date, 8/1 and 12/1 are more than 90 <tbody> </tbody>
12/01/2014	12/1/2015 get credit <tbody> </tbody>
12/24/2014	after 90 days <tbody> </tbody>

<tbody>
</tbody>

So, far I have created the following.
Tables
Employees: Employee_ID, Last_Name, First_Name
Points: Point_ID, Point_Description, Point_value
Attendance: Attend_ID, Attend_Date, ID_Employee, ID_Point.

QUERY POINTBALANCE_SUB1
SELECT Attendance.ID_Employee, Attendance.Attend_Date, DMax("[Attend_Date]","Attendance","[ID_Employee]=" & [ID_Employee] & " AND [Attend_Date]<#" & [Attend_Date] & "#") AS LastInfraction, iif(isnull(LastInfraction), Attend_Date, DateValue(LastInfraction)) AS cal, iif((Attend_Date - cal) >=90, 0, 1) AS Point_Value, Attend_Date+90 AS NextInfraction
FROM Attendance, Points
WHERE Attendance.ID_Point = Points.Point_ID
ORDER BY Attendance.ID_Employee, Attendance.Attend_Date;

POINTBALANCE_SUB2

SELECT Employees.Employee_ID, Employees.First_Name, Employees.Last_Name, Sum(PointBalance_sub1.Point_Value) AS SumOfPoint_Value
FROM Employees INNER JOIN PointBalance_sub1 ON Employees.Employee_ID = PointBalance_sub1.ID_Employee
GROUP BY Employees.Employee_ID, Employees.First_Name, Employees.Last_Name;

The only issue I have with the yearly point, I am unable figure out how I will calculate using the right formula. will anyone help me out.

Thanks in Advance

 

[Gingertrees]

First: commentary to consider if you are management, or to consider mentioning to management if you're not but you're brave:
2nd time this has come up on the board in 2 weeks - why do places adopt systems like this? I've worked under a system like this, and it makes good employees neurotic/joyless, while making everyone drive recklessly if they encounter any delays. Further, if you guys are a small enough company that your hour system is a spreadsheet - there are SO MUCH BETTER WAYS to get people to show up on time.

Second: your calculation difficulties will vanish if every incident just has an expiration date. That would allow you to delete the whole "LastInfraction" part of your calculations. [It really is such a double whammy if your incident is about to expire and OOPS traffic jam or your kid makes a mess at the last second or (etc.).]
Then on each employee's anniversary, you can just add (delete?) 1 point to employee total.

 

[maabbas]

Thanks Gingertrees, I will inform your thoughts to our management group which I am not part of. By the way, learned SQL 15 years ago (but did not work on it) trying to go back to write some query and make little place in my company. Thanks again for your useful tips. any other tips gladly appreciated.

 

[Gingertrees]

I don't know if my comments will help much - I'm less familiar with SQL than VBA. Also, I'm just a random internet person.

Good luck.

 

[maabbas]

SO, MR. Random Internet Person will have something which can be done in VBA?

 

[Gingertrees]

Not with the continual recalculations - see if your management is willing to consider just "every incident has an X-day lifespan" due to its awfulness and difficulties in calculations. At that point, the calculation is a simple Sum If (DATE-incidentdate)<90.

 

[HiTechCoach]

This also looks like yours: Employee attendance by points System - Microsoft Community

It is beyond a simple spreadsheet or Access queries.

This is possible (there are solution available see links below) but will require intermediate to advanced programming skills and lots of time. This will probably require VBA coding with recordsets.

This really should be part of your Timeclock/Payroll/HR system. Have you ask your vendor is they have a solution?

You may want to check out these ready to use solutions:

Attendance on Demand time and attendance systems
Managing Attendance with Points

Attendance Points Manager: Customizable and Intuitive Employee Attendance Tracking Software Program

 

[maabbas]

Thanks! HighTechCoach I will check it out.

 

[HiTechCoach]

You're welcome.

Good luck with your project.