Access 2010 database query

5 pts.
Tags:
Access 2010
Access Database
Access Database Attachments
Access 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
ASKED: June 15, 2011  5:11 PM
UPDATED: June 16, 2011  2:33 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following