New to this 1

[Tlouise]

I am working on compiling attendance records for over 100 employees. I need a way to look at the occurances on my table and have 2 attendance points deducted after a period 60 calendar days. 2 more points for every 30 days of good attendance there after.

 

[Melagan]

I need a way to look at the occurances on my table and have 2 attendance points deducted after a period 60 calendar days.

The occurances of what on your table? This doesn't sound too difficult but I think we need a bit more information. How does this point system work? Are your employees deducted points from being absent, but regain points after 30 days of good attendance? Explain your system in a bit more detail and help will be on the way =)

~Melagan
______
"It's never too late to become what you might have been.

 

[OrbitMascot]

Melagan,
I am working on a similar project. I have an attendance tracker table that keeps track of all occurances that an employee might receive. Ex. late more than 5 minutes, no/call, unexcuse absense, etc. Each occurances racks up a different amount of points 2, 4, or 8. Then if the employee has perfect attendence for 60 day they get 2 point taken away. When they reach 90 days another 2 points are taken away, then the process starts over again (ex in the next 60 and 90 days). Until the employee is back to 0 points. I am not sure what the best solutions to this is. The current table, shows the employeesname, what type of occurance, and the date the occurance happened.
Any suggestions would be greatly appreciated.

Thanks,
OrbitMascot

 

[PaulBricker]

Do you keep a separate record for points accumulated and points deducted? How is that handled? Do you want the system to automatically update(add a new record)when an occurance takes place or an attendance deduction passes a certain number of days? Do holidays count as continuous days of work? The solution is really going to be in the details.

Paul

 

[OrbitMascot]

Paul,
Thanks for the response. Currently I am only keeping track of the points accumulated. I just created a query that combined the occurance table with the points table, to tell me how many points each occurance is worth. So if someone is 5 minutes late, the query lets me know they have 2 points against them.

I do not have a table set up yet for the deducted points.

I would like the system to automatically subtract the deducted points. From the acumulated points on a daily or weekly basis.

As for the perfect attendance. Every day counts including weekends and holidays. It is actually done on a three month process, so people get 0 points for one month 2 points for 2nd month and an additional 2 points for the 3rd month.

I hope that answered all your questions.

Thanks,
OrbitMascot

 

[PaulBricker]

That says to me you are not storing the point value in what I'll call an occurance record in a table. That it exists in the query because of the join. That is fine. It makes sense that you don't need to store that value when you can easily retrieve it in your query.
So lets take this one step at a time.
Create a new query using your existing query as the source, you can turn on the totals, group by employeesID, set the total line to Last or Max for the occurance date and Sum the accumulated points. Then on the Field line put something like this.

ContinMonthsateDiff("m",LastOccuranceField,Date())

This should give you the number of months since the last occurance.
Now, we will need to store the deducted points value in a table someplace. Maybe you could set up a little test Db and work in that till we get it straightened out. Once you do that, let me know and we'll take the next step.

Paul

 

[OrbitMascot]

Paul,
We're good to go.

OrbitMascot

 

[PaulBricker]

OK, I assume if you have an occurance table that it might make sense to have a deducted points table with fields like EmpId, deductDate and ptsDeducted. One question. If someone goes 4 or 5 or 6 months without an absence, what happens? Do they continue to get addtional points deducted?
We will need some way to document what points are applied to what time periods so that we don't lose track of deductions earned.
Once we get there, it should come together fairly easily.

Sorry, it's the end of my day. I'll be home in a couple hours and will check back then. I'll have time over the weekend to work with this.

Paul

 

[OrbitMascot]

Paul,
Thanks so much for your help, I am also getting ready to go home, so I will check back in on Monday. But to answer your question. Everything is based on three months. So when you get to the fourth month, you are actually back to month 1, so you wouldn't get any points deducted, but in the 5th month you would get 2 points.

Months Point deducted
1 4 7 10 0
2 5 8 11 2
3 6 8 12 2

I hope that helps.

Thanks again,
OrbitMascot

 

[PaulBricker]

OK, I've looked at this for a few hours and I have not really decided on exactly what method is best(and that may depend on how you have set up your tables) so I'm going to back up to the beginning.
The first thing I would like to say is "Normalized tables are always the best way to approach any situation". So in this case, the normalized table structure might look like this.

[blue]1. tblEmp with fields like EmpID(PK), Name (last and first), Address etc.[/blue]
This would be you standard table to input your employees.

[blue]2. tblEmpHistory with fields like EmpID(FK), Action, actDate[/blue]
This table would hold the Employee identifier as the Foreign Key, something that identified the action, in our case either an Occurance like Late, unexcused Absence, No Call, etc. or and Action I will call Deduct. I'll get more specific later

[blue]3. tblAction with fields Action(PK), and Points.[/blue]
This would be like you Occurance table except that you would have Primary Key Actions and not Occurances and you would have Actions like Late, Unexcused Absence, No Call and you would add an additional record for "Deduct" with a point value of -2

Now, this is where we need to decide how to input data. I assume that you will input you Occurance's thru some type of form. So if someone is late or has an unexcused absence, that info would be input by a form and it would go into a table like tblEmpHistory.
The issue then is how to automatically put in info into tblEmpHistory when a Deduct action has taken place. I'm still working on this, but wanted to get your input on what you thought about the above table structure. As I said earlier, I have another way to deal with this but thought the normalized table would be better.
Let me know and in the mean time I'll work on a method to automatically update the Deductable values.

Paul

 

[OrbitMascot]

Paul,
I above structure is good. I already have a tblEmployee and a tblEmpHistory. I will set up a tblActions. Thanks again for your help.

Thanks,
OrbitMascot

 

[PaulBricker]

Just to let you know I'm still working on this. The issue has been the automatic updates to the Deduct records. I've looked at strictly a query solution and strictly a code solution, and neither works well. Now I'm working on a combination of the two to get there.
Hopefully sometime today depending on how my day shapes up. I'll let you know.

Paul

 

[PaulBricker]

Things are looking pretty good. I just need to find out how you figure your months. If an employee is late on 11/30/06 and gets occurance points, what date does the employee earn deduct points. I assume that it's 1/30/07. But there may be some other way you handle this. Also if an employee is late 12/30/06 there would be no corresponding 2/30/07 to issue deduct points.
I think I need to find a substitue for the DateDiff() function. If I have two dates, say 11/30/06 and 1/9/07, DateDiff says that we have 2 months difference. I don't think that will work for your needs.
Anyway,we're close so let me know how you calculate your months and I'll try and finish this up.

Paul

 

[OrbitMascot]

Paul,
As for months we calculate just like regular months, so 10/30/06 the first points would be deducted on 12/30/06 then again on 1/30/07. As for Feb we would probably just go with the last day of the month be it the 28th or 29th. For example: if an occurance happens on 12/30/06 they would get deducted points on 2/28/07 then again on 3/30/07. If it would be easier, we could do in days, so every 60 and 90days points get deducted. Either way it will be about the same time frame.
Thanks again for all your help.

OrbitMascot

 

[PaulBricker]

OK, here it is.
First, we have the 3 tables described above. This will keep all your actions (occurances and deducts) in the same table.
Then we create queries. Here is the sql for them

SELECT tblEmpHistory.EmpID, Max(tblEmpHistory.actAction) AS oAction
FROM tblEmpHistory
WHERE (((tblEmpHistory.Action)<>"Deduct"))
GROUP BY tblEmpHistory.EmpID;

SELECT tblEmpHistory.EmpID, Max(tblEmpHistory.actAction) AS dAction
FROM tblEmpHistory
WHERE (((tblEmpHistory.Action)="Deduct"))
GROUP BY tblEmpHistory.EmpID;

SELECT qryMaxOccDates.EmpID, qryMaxOccDates.oAction, getMths([oAction]) AS Mths, nz([dAction],[oAction]) AS dtAction, IIf([oAction]>[dtAction],DateDiff("m",[oAction],Date())-1,DateDiff("m",[dtAction],Date())-1) AS dteAction
FROM qryMaxOccDates LEFT JOIN qryMaxDeductDate ON qryMaxOccDates.EmpID=qryMaxDeductDate.EmpID;

and

SELECT qryLAction.EmpID, qryLAction.oAction, qryLAction.Mths, qryLAction.dtAction, qryLAction.dteAction
FROM qryLAction
WHERE (((qryLAction.dteAction)>0));

The first query is named qryMaxOfOccDates
The second one is named qryMaxDeductDates
The third is qryLAction
The forth is qryLastAction

The forth one just filters the data in the third one, but I had to filter on a calculated field so I couldn't do it in the same query.

Then I have two procedures

Function getMths(sDte As Date) As Integer
Dim i As Integer
    i = 0
        Do While sDte < Date
            sDte = DateAdd("m", 1, sDte)
            i = i + 1
        Loop
 getMths = i - 1

End Function

and

 Function updateTable()
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("qryLastAction", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("tblEmpHistory", dbOpenDynaset)
    If rst.EOF Or rst.BOF Then
        Exit Function
    Else

        rst.MoveFirst
    Do Until rst.EOF
        Select Case rst!Mths
            Case 0, 1, 4, 7, 10
                rst.MoveNext
            Case 2, 3, 5, 6, 8, 9, 11, 12
                rst2.AddNew
                rst2!EmpID = rst!EmpID
                rst2!actAction = DateSerial(Year(rst!oAction), Month(rst!oAction) + rst!Mths, Day(rst!oAction))
                rst2!Action = "Deduct"
                rst2.Update
                rst.MoveNext
            End Select


    Loop
    End If
End Function

Now here is what happens. You have all the occurance and deduct records in the same table so the first query finds the Max (last) occurance date for each employee, and the second query finds the last deduct date for each employee.
The third query does a couple things. First, if there is an occurance for and employee but no deduct record, it handles the null deduct field so we see a record for every occurance. Then it checks to see when the last Action was so we can filter out any records that have been updated in the last month. Otherwise, if you ran the code to update, it would update employees multiple times. This way, once they are updated, they get filtered out of the query until it's time to update them again. My only concern is the DateDiff function my not be entirely trustworthy. I will look at it some more, but wanted to get something to you to work with in the interim.
The last query just filters the records from the 3rd query as mentioned above.
Now the first procedure just checks the number of months since the last occurance.
The second procedure looks at the number of months since the last occurance, and adds another deduct record based on the number of months since the last occurance and the last deduct record.
The code only handles up to 1 years time. If we need more than 12 months, let me know, we'll have to expand it. The procedures can be stored in the same module. The second one calls the first one when it looks for the value for rst!Mths so having them in the same module will speed things up (fractionally).

Look it over and post back with specific problems.
There are probably other ways to accomplish this and it's still a little clunky, but it's a starting point.

Paul

 

[OrbitMascot]

Paul,
It is working great. I just had to change a couple of the wording, due to the tables I created were names slightly different from yours. But it is working great. Thanks so much for your help. You get a PURPLE star for all your help.

Thanks again.
Orbitmascot

 

[PaulBricker]

Thanks. I did a little testing with the DateDiff Function in the query qryLAction. I ended up sending that field out to the same getMths() function to get the values instead. I liked that a little better, but either might work just as well.
Post back if you have any specific problems.

Paul