Query to Calculate Points based on Dates (1 Viewer)

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
Hello Everyone, I need your guys help to start, I am trying to create an attendance database, our company introducing the point system attendance,

Called Off (CO) 2 points
Left Early (LE) 1 Point
Tardy (Tar) 1 point

Employee can reduce point if they have perfect attendance for 90 days from the last day of violation.

For example, an employee absent on 01/01/2015, he will received 2 points, the credit will giving on 04/01/2015, if there is no violation, but if he absent again on 03/31/2015 not only he will received 2 more points his 90 days will start from 03/31/2015, now he will eligible to get credit on 06/29/2015 and so on.

So far I have created 3 tables and 1 query.
Tables

Employees: Id, Last Name, First Name
Points: Id, Description Points
Attendance:Id, Date, Employee ID, Points ID

Query
Date
Employee ID
Last Name
First Name
Point Description
Points

Will someone help to figure out how and which formula to use which calculate the points based on above example, thanks in advance
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
I believe what you want is fully calculable with the structure you have but not in the manner you proposed. Your explanation doesn't mention that a record will be created in Attendance if the employee shows up for work on time and stays until required (essentially a 0 point event). To accomplish what you want with only queries, you will need to track regular attendance in Attendance as well.

Is that the case and you just omitted that? Or are only point assesments going into Attendance?

Can you provide some sample data from Attendance? Then, using that sample data, show what you expect the query to return.
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
Thanks for reply, the main object is to calculate points, I have only test data,
Attandance Date Employee ID Last Name First Name Description Points
1/1/2015 1 Abbas M CO 2
3/31/2015 1 Abbas M CO 2

the 90 days is 06/29/2015 based on last violation, he will received 2 points credit on 06/29/2015 and the next on 09/27/2015 (which is in future). I want to make another query to calculated this. Hopefully I explain right.
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
You neither answered my questions, nor provided the data I requested. You iterated your issue. Let's try again.

1. Are only point assesments going into Attendance? Or does attendance also get a record for a person on a date for which they attended work and received no points?

2. Can you provide some sample data from Attendance? Then, using that sample data, show what you expect the query to return if it was run today. Provide enough data to cover all cases. Hint: I expect to see 2 sets of data (1 from your table, and then what you expect the query to return based on that data)
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
1. Yes only point assessment going into attendance
2. Here is data from Table

Attandance
ID Attandance Date Employee ID Points ID
1 1/1/2015 1 2
2 3/31/2015 1 2
5 4/2/2015 1 2
6 1/4/2015 2 2
7 6/30/2015 2 3

Here what I am expected if query run today
QryVoilationPoints
Employee ID Last Name First Name Total Incident Violation Points
1 Abbas M 6 4
2 Bryant A 3 1

The violation point what I need to calculat.
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
That helps. I think I have it, but let me give you an example and you tell me what the results would be:

Attendance
EmployeeID, Date, PointID
1, 1/15/2015, 3
1, 2/15/2015, 3
1, 3/15/2015, 3
1, 4/15/2015, 3
1, 5/15/2015, 3
1, 6/15/2015, 3

On 7/1/2015 does Employee=1 have 3 points or 6 points?
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
That helps. I think I have it, but let me give you an example and you tell me what the results would be:

Attendance
EmployeeID, Date, PointID
1, 1/15/2015, 3
1, 2/15/2015, 3
1, 3/15/2015, 3
1, 4/15/2015, 3
1, 5/15/2015, 3
1, 6/15/2015, 3

On 7/1/2015 does Employee=1 have 3 points or 6 points?

he will have 6 points
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
First, you've got some poorly named fields--you shouldn't put spaces in them(Last Name), instead use capitalization (LastName) or underscores (Last_Name). It just makes writing code like this easier.

Attached is a database that does what you want. I had to do this in a few sub-queries and those are noted as such. The only query you need is the one called PointBalance.

Play around with the values in the underlying tables and see if it does it correctly.
 

Attachments

  • test.accdb
    512 KB · Views: 131

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
First, you've got some poorly named fields--you shouldn't put spaces in them(Last Name), instead use capitalization (LastName) or underscores (Last_Name). It just makes writing code like this easier.

Attached is a database that does what you want. I had to do this in a few sub-queries and those are noted as such. The only query you need is the one called PointBalance.

Play around with the values in the underlying tables and see if it does it correctly.

Thanks you very much for your help, I will test the file and let you know
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
please see the table Attendance

Attend_ID Attend_Date ID_Employee ID_Point
1 1/1/2015 1 1
7 7/1/2014 2 2
8 9/1/2014 2 1
9 1/1/2015 2 1
10 2/1/2015 2 1
11 6/21/2015 2 1
12 2/1/2015 3 3
14 4/5/2015 1 1

I change some data to test, see ID_Employee "1" he has absent on 01/01/2015 and 04/05/2015, he suppose to have 2 points because he got credit on 04/01/2015, but query showing 4 points.

Employee_ID PointBalance
1 4
2 9
3 0
4 0
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
Replace the SQL in PointBalance_sub3 with this:

Code:
SELECT Employees.Employee_ID, IIf(IsNull([TotalPoints]),0,[TotalPoints]) AS PointBalance
FROM Employees LEFT JOIN PointBalance_sub3 ON Employees.Employee_ID = PointBalance_sub3.ID_Employee;
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
Replace the SQL in PointBalance_sub3 with this:

Code:
SELECT Employees.Employee_ID, IIf(IsNull([TotalPoints]),0,[TotalPoints]) AS PointBalance
FROM Employees LEFT JOIN PointBalance_sub3 ON Employees.Employee_ID = PointBalance_sub3.ID_Employee;

getting "circular Reference caused by PointBalance_Sub3" error
 

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
Sorry, copied the wrong SQL. This is the good SQL for PointBalance_Sub3:

Code:
SELECT PointBalance_sub1.ID_Employee, Sum(IIf([Attend_Date]>=[InfractionStart],[Point_Value],0)) AS TotalPoints
FROM PointBalance_sub1 INNER JOIN PointBalance_sub2 ON PointBalance_sub1.ID_Employee = PointBalance_sub2.ID_Employee
GROUP BY PointBalance_sub1.ID_Employee;
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
Sorry, copied the wrong SQL. This is the good SQL for PointBalance_Sub3:

Code:
SELECT PointBalance_sub1.ID_Employee, Sum(IIf([Attend_Date]>=[InfractionStart],[Point_Value],0)) AS TotalPoints
FROM PointBalance_sub1 INNER JOIN PointBalance_sub2 ON PointBalance_sub1.ID_Employee = PointBalance_sub2.ID_Employee
GROUP BY PointBalance_sub1.ID_Employee;

Thanks! my friend you are a Star, so far its working and not find any issue. Thanks! again
 

maabbas

Registered User.
Local time
Today, 08:21
Joined
Feb 15, 2012
Messages
69
sorry I post the wrong Senario
 
Last edited:

plog

Banishment Pending
Local time
Today, 10:21
Joined
May 11, 2011
Messages
11,613
I had to add 2 new sub-queries to accomodate that situation. Attached is a revised database that now works for those cases. Let me know if you find any other issues.
 

Attachments

  • test.accdb
    504 KB · Views: 134

Users who are viewing this thread

Top Bottom