Access 2010 database query
I am new to Access and am having some trouble writing a query, for an absentee database. I only have two tables, one for the roster and the other with the absentee logs. The Roster table only has employee name, badge number, and shift. The Attendance Log table has the date of occurrence, the employee badge number, points accumulated (either 0.5 or 1), reason, and comments. Apart from the point values I would like to add the disciplinary level, which is as such,• Level 1 - Oral Warning (2 points within thirty (30) days) • Level 2 - Written Warning (1 point within thirty (30) days of Step 1) • Level 3 - Three (3) Day Suspension (1 point within thirty (30) days of Step 2) • Level 4 - Dismissal (1 point within thirty (30) days of Step 3) But also, 30 days goes by and no point has occurred than it reverts back to the previous level. I would like to write a query that calculates and assigns the disciplinary levels, but am not sure how to go about it. I need someone to point me in the right direction. Hoping help is out there, Katie
Software/Hardware used:
2010 Microsoft Access
Software/Hardware used:
2010 Microsoft Access
ASKED:
June 15, 2011 5:11 PM
UPDATED:
June 16, 2011 2:33 PM
Answer Wiki:
You will probably need to do this with mutlple queries. I'm just thinking without testing it and maybe this is over simplifed right now; but it will get you thinking.
I would probably store the level in the roster table and a date when it was last changed. Then, make a query that would get the max(occurancedate) from the occurance log table for each person. Then, make a second query that joins the max date query with the roster table on employee badge with crtiera that checks for less than or equal 30 days between the max date and the level change date (datediff function). You could make that second query an update query that would add 1 to the level and update the level change date with the max date. People who didn't get an occurance record within 30 days of their last one will not be included in the update. Make another query that would find the people whose last occurance date was more than 30 days from that last change and update the level by subtracting 1. Maybe that level change date would be the date you changed the level.
good luck
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
