Code to update an attendance table.

Hi Ty,

will have a look at it a bit later, what has changed? Just the add new record part?

Alan

1. UPDATE tblOccurrence
SET [Occurrence Dropped] = 1
WHERE (((tblOccurrence.sDate)<Date()-365));

2.
UPDATE tblOccurrence
SET [Occurrence Dropped] = 1
WHERE [OccurrenceValue] = 0

What has changed is when a 4 month span of no records are found we are dropping the oldest record.  That would be great if all records were a value of 1 but in this environment the values can be .25 , .5, .75, and 1.   What we need to do is drop one full occurrence totaling a value of 1.  The only way I can think of doing it is to add a record with a value of -1 so as to credit the employee.

Also there can be a record with a value of 0 that should not be counted as a record.  These are excused and should not be counted as a record when lookinf for a 4 month span.

3a.
SELECT tblOccurrence.Employee, Max(tblOccurrence.sDate) AS MaxOfsDate
FROM tblOccurrence
WHERE (((tblOccurrence.[Occurrence Reference])=-1) AND ((tblOccurrence.sDate)>Date()-365))
GROUP BY tblOccurrence.Employee;

I was waiting that to come up, noticed it when I read the rules. LOL

Can we derive the .25, .5, .75 from the comments field, meaning do certain comments = certain values.
Coz we can actually store any non zero integer value in the checkbox field as true.

Alan

Umm .. What 'values' are you talking about? There aren't any fields except the text that would support entering .25, .5, .75, and 1.

In your next comment you say "there can be a record with a value of 0" don't you mean a field .. only which one?

I get the impression I'm a third wheel here.

should I bother expending any more effort on this, or are you just going to give all the points to alan anyway?

Hi Frosty,

Not third wheel at all, most welcome you are.
Question has long history: https://www.experts-exchange.com/questions/20952366/VB-Code-to-mark-table-for-Employee-Attendance.html

Alan

Went to bed as it is approaching 1:00am here but the dog barked wanting out so I had to peek in.  

I welcome any help as I am truely a novice with an HR manager that wants results.  By occupation I am a specialist in network administration, and business systems implementation......by default (of the bosses) I am now an access programmer. LOL   Without the experts support here I would not succeed.  As alanwarren indicated this has been a long and difficult task.  If you look back through the previous questions (3 or 4 now I think) you will see I have tried all responses.  If I am able to use someone's code they will be rewarded for it.

you still haven't answered my question. I'm going to bed too.

ala frosty.....
I interpreted your question as asking if you were wasting your time.  I welcome any help I can get.  I just tried your code for 1 and 2 and it works thus far but the third obstacle is quite a doozy.

alanwarren.....
The code from the previous questions work for 1 and most of 3.  The 2 is a new issue as of this question.

Morning....  Had one of those thoughts last night that maybe I am confusing those that are assisting me with this issue.  I have been trying to give the logic but maybe if I just give the requirements it may make more sence.

Here it Goes.

Employees are graded for their attendance.  Too many occurrences gets them in trouble or terminated.
The Occurrences are counted from the current date back 1 year.  To encourage the employees awards are given for 4 months of perfect attendance in the form of removing 1 full occurrence and no more than three occurrences can be given in a year.  If an employee does not have 1 full occurrence then they are put back to zero.

Hi Ty,

Just about to knock off for the night here.

Been thinking about the points scale for this attendance.
Seems we store only two comments Call In and Over 2, unless you have other comments in your full dataset.
Guess I was hoping we may be able to use the comments to run an update sql to create a demerrit points field for the .25, .5, .75 then it would be summable. It is a bit difficult to apply this part of the employment attendance rules when we have not been collecting the data required. If you can figure out a logic to apply whereby we can generate the values, that would be good. Other than that we would probably be guessing, not a good stance to adopt when peoples jobs are involved, unless we err on the side of caution. If in doubt .25. hmmm...

Alan

What other Occurence types do you have in your data?

Okay, so I'll reiterate my question because I guess it was hard to interpret as a question especially with the question mark on the end:

What 'values' are you talking about? There aren't any fields except the text that would support entering .25, .5, .75, and 1.

Hi ala_frosty


From:
https://www.experts-exchange.com/questions/20947819/2-update-Querys-that-conflict-with-each-other.html
======================================
Absenteeism
Absences from work will be considered in terms of “occurrences,” with the following number of occurrences for each type of absence.
 
1 occurrence for each period of absence from work. (consecutive scheduled work days)
For example:  An employee who misses Monday, Tuesday, and Saturday but is scheduled off Wednesday, Thursday, and Friday, will get one occurrence.  If the employee is absent, then works, and is absent again, the days are not consecutive and there will be two occurrences.

 (Requires a medical doctor's excuse, on a Company-supplied form, for absences longer than 3 days.)  .

¼  occurrence for each late arrival or early departure of less than one (1) hour.


½  occurrence for each late arrival or early departure of less than two (2) hours.

1 occurrence for late arrival or early departure greater than two hours

2 occurrences for each unexcused absence.

One occurrence will be removed with perfect attendance for four consecutive months.  Perfect attendance is defined as no tardies, early departures, or absences chargeable under the attendance policy.  Up to three occurrences may be removed per year.  The oldest occurrence will be removed under this system.  An employee cannot go below zero on the occurrence system.  Employees with “perfect attendance” will earn one extra personal day for the coming year.  (Note:  “Perfect” means the employee works a minimum of 100 days from April 1 to December 31, has no occurrences due to absence from work, and has no more than ½ occurrence for late arrivals or early departures.
============================================



this is a snpshot of the data we are dealing with:
======================================
ID     Employee                       Date     Value     Type    Comments   Dropped   Reference
10     Thayer, Trygve     1/2/1998     1     Call In          TRUE     FALSE
11     Thayer, Trygve     1/2/1999     1     Call In          TRUE     FALSE
12     Thayer, Trygve     1/2/2000     2     Call In          TRUE     FALSE
6     Thayer, Trygve     1/1/2001     2     Over 2      tttt  TRUE     FALSE
7     Thayer, Trygve     1/1/2001     2     Call In  eeee     TRUE     FALSE
13     Thayer, Trygve     1/2/2001     1     Call In          TRUE     FALSE
8     Thayer, Trygve     1/1/2002     1     Continue     wwww TRUE     FALSE
14     Thayer, Trygve     1/2/2002     1     Call In          TRUE     FALSE
5     Thayer, Trygve     3/30/2002     2     Call In          TRUE     FALSE
9     Thayer, Trygve     1/1/2003     1     Under 1     rrrr  TRUE     FALSE
15     Thayer, Trygve     2/1/2003     1     Call In          TRUE     FALSE
16     Thayer, Trygve     3/1/2003     1     Call In          TRUE     FALSE
17     Thayer, Trygve     4/1/2003     1     Over 2           TRUE     FALSE
1     Thayer, Trygve     4/10/2003     2     Call In  yep h   TRUE     FALSE
25     Thayer, Trygve     4/11/2003     1     Call In         TRUE     FALSE
18     Thayer, Trygve     5/1/2003     2     Over 2           FALSE    FALSE
19     Thayer, Trygve     9/1/2003     1     Over 2           FALSE    FALSE
23     Thayer, Trygve     11/1/2003     2     Call In         FALSE    FALSE
=================================================

hth

Alan

Actually the data snapshot is incorrect; posted above is from a query resultset with named aliases.

this is the current data that I have:

OccurrenceID      Employee      Date      Occurrence Value      Occurrence Type      Occurrence Comments      Occurrence Dropped      Occurrence Reference
1      Thayer, Trygve      10-Apr-2003      2      Call In      yep he did it.      Yes      No
5      Thayer, Trygve      30-Mar-2002      2      Call In            Yes      No
6      Thayer, Trygve      01-Jan-2001      2      Call In      tttt      Yes      No
7      Thayer, Trygve      01-Jan-2001      2      Call In      eeee      Yes      No
8      Thayer, Trygve      01-Jan-2002      1      Call In      wwww      Yes      No
9      Thayer, Trygve      01-Jan-2003      1      Call In      rrrr      Yes      No
10      Thayer, Trygve      02-Jan-1998      1      Call In            Yes      No
11      Thayer, Trygve      02-Jan-1999      1      Call In            Yes      No
12      Thayer, Trygve      02-Jan-2000      2      Call In            Yes      No
13      Thayer, Trygve      02-Jan-2001      1      Call In            Yes      No
14      Thayer, Trygve      02-Jan-2002      1      Call In            Yes      No
15      Thayer, Trygve      01-Feb-2003      1      Call In            Yes      No
16      Thayer, Trygve      01-Mar-2003      1      Call In            Yes      No
17      Thayer, Trygve      01-Apr-2003      1      Call In            Yes      No
18      Thayer, Trygve      15-May-2003      2      Over 2            Yes      No
19      Thayer, Trygve      11-Aug-2003      1      Over 2            Yes      No
23      Thayer, Trygve      11-Dec-2003      2      Call In            No      Yes
25      Thayer, Trygve      15-Apr-2003      1      Call In            Yes      No
27      Thayer, Trygve      12-Apr-2004      2      Call In            No      Yes
29      Ausmus, Adam      14-Apr-2004      1      Call In            No      No


Alan

Hi ala_frosty,

The combination of half occurences and quarter occurences is what we are discussing when referring to the .25, .5, .75
These values are not stored anywhere, but are implied by fields [Occurrence Type] and [Occurrence Comments]

Hope that kinda brings you up to speed :)

Alan

I see .. I failed to read into your question. Just a minute while I fetch my psychic hat.

Maybe next time you ask a question related to another one with a gazillion postings, you could either refer to it from the start, or include the information so people coming in fresh could know they're on a steep learning curve where the question you ask is not actually the question you want solved.

For example, people here are talking about fractional occurences. Your data types not only do not support fractional occurences, but they, in no way, imply fractional occurences to anyone who is not already in your mindset.

Have some consideration for people in general please. If a question needs a priori knowledge indicate such in the question.

Hello again after 14 hours.  Had to leave unexpectedly and just got back.  Thanks in advance for all your help.  I will catch up with your suggestions and get back with you as soon as I can.

ala frosty.......

after reading your comments I am without words to say.  I am seeking help as I do not have a background in access as commented above.  The requirements listed above are probably the most accurate of what I am needing.  IF that is provided then my issue will be resolved.  In defense of alanwarren he already has code he has been working on and was most considerate as he gave you the link to review what has been going on.  I interpret his questions as trying to identify if he can modify what we have from the previous questions.


alanwarren.....

In the field [Occurrence Type] I have the following  Call In, Continuance, UnExcused, Excused, Under 1 hour, Under 2 hours, Over 2 hours,and Award.

In the field [Occurrence Value] I have typed in values of .25, .5, .75, and 1 but the table seems to round it up.  I was going to work on it to get is displaying properly but had set it aside to see if I could get the code to update it properly.  One thing in reading your comments  

"sql to create a demerrit points field for the .25, .5, .75 then it would be summable"

I am under the impression you are looking at how multiple records could be dropped to equal a value of 1.  I don't think that would work as we might have a value in 4 records (going from the oldest as .5, 0, .75, .25)  the first 3 records would be over a value of 1.  So my thoughts are to insert a record with a value of -1 on the ending date where a 4 month span was found.   11/1/2003, 11/5/2003, 3/31/2003, 4/15/2003.  The 4 month span would be from 11/5/2003 to 3/5/2004 so the record added would have a date of 3/5/2004 as that is the actual date the employee would be awarded a value of 1 to be dropped.  

I am going to go back through the logic and see if I can think of a simpler way of doing this.  If you think fields need to be added or dropped that is ok with me.

I have created a new data set and fixed my value field in the table by setting [Occurrence Value] to currency instead of long integer and set the format as Standard with 2 decimal places.  Hope that was the right way.

30      Ausmus, Adam      3/14/2003      2.00      UnExcused                            FALSE      FALSE
10      Thayer, Trygve      1/2/1998      1.00      Call In                            FALSE      FALSE
11      Thayer, Trygve      1/2/1999      2.00      UnExcused                            FALSE      FALSE
12      Thayer, Trygve      1/2/2000      0.25      Under 1 Hour            FALSE      FALSE
6      Thayer, Trygve      1/1/2001      1.00      Call In      tttt                      FALSE      FALSE
7      Thayer, Trygve      1/1/2001      2.00      UnExcused      eeee                      FALSE      FALSE
13      Thayer, Trygve      1/2/2001      0.50      Under 2 Hours            FALSE      FALSE
8      Thayer, Trygve      1/1/2002      0.25      Under 1 Hour      wwww      FALSE      FALSE
14      Thayer, Trygve      1/2/2002      1.00      Call In                            FALSE      FALSE
5      Thayer, Trygve      3/30/2002         0.50      Under 2 Hours            FALSE      FALSE
9      Thayer, Trygve      1/1/2003         0.50      Under 2 Hours      rrrr      FALSE      FALSE
15      Thayer, Trygve      2/1/2003         2.00      UnExcused                            FALSE      FALSE
16      Thayer, Trygve      3/1/2003         0.25      Under 1 Hour            FALSE      FALSE
17      Thayer, Trygve      4/1/2003         0.50      Under 2 Hours            FALSE      FALSE
1      Thayer, Trygve      4/10/2003         0.25      Under 1 Hour         he did it.      FALSE      FALSE
25      Thayer, Trygve      4/15/2003         0.50      Under 2 Hours            FALSE      FALSE
18      Thayer, Trygve      5/15/2003         1.00      Call In                            FALSE      FALSE
19      Thayer, Trygve      8/11/2003         2.00      UnExcused                            FALSE      FALSE
32      Thayer, Trygve      10/2/2003         0.00      Excused                            FALSE      FALSE
23      Thayer, Trygve      12/13/2003  1.00      Call In                                       FALSE      FALSE
33      Thayer, Trygve      12/14/2003  0.00      Continuance                      FALSE      FALSE
34      Thayer, Trygve      12/15/2003  0.00      Continuance                      FALSE      FALSE
27      Thayer, Trygve      4/17/2004        1.00      Call In                            FALSE      FALSE


When updated if the current date is 4/17/2004  it should look like this

30      Ausmus, Adam      3/14/2003      2.00      UnExcused                            TRUE      TRUE
10      Thayer, Trygve      1/2/1998      1.00      Call In                            TRUE      TRUE
11      Thayer, Trygve      1/2/1999      2.00      UnExcused                            TRUE      TRUE
12      Thayer, Trygve      1/2/2000      0.25      Under 1 Hour            TRUE      TRUE
6      Thayer, Trygve      1/1/2001      1.00      Call In      tttt                      TRUE      TRUE
7      Thayer, Trygve      1/1/2001      2.00      UnExcused      eeee                      TRUE      TRUE
13      Thayer, Trygve      1/2/2001      0.50      Under 2 Hours            TRUE      TRUE
8      Thayer, Trygve      1/1/2002      0.25      Under 1 Hour      wwww      TRUE      TRUE
14      Thayer, Trygve      1/2/2002      1.00      Call In                            TRUE      TRUE
5      Thayer, Trygve      3/30/2002         0.50      Under 2 Hours            TRUE      TRUE
9      Thayer, Trygve      1/1/2003         0.50      Under 2 Hours      rrrr      TRUE      TRUE
15      Thayer, Trygve      2/1/2003         2.00      UnExcused                            TRUE      TRUE
16      Thayer, Trygve      3/1/2003         0.25      Under 1 Hour            TRUE      TRUE
17      Thayer, Trygve      4/1/2003         0.50      Under 2 Hours            TRUE      TRUE
1      Thayer, Trygve      4/10/2003         0.25      Under 1 Hour         he did it.      TRUE      TRUE
25      Thayer, Trygve      4/15/2003         0.50      Under 2 Hours            TRUE      TRUE
18      Thayer, Trygve      5/15/2003         1.00      Call In                            TRUE      TRUE
19      Thayer, Trygve      8/11/2003         2.00      UnExcused                            FALSE      FALSE
32      Thayer, Trygve      10/2/2003         0.00      Excused                            FALSE      FALSE
                Thayer, Trygve          12/12/2003   -1          Award                                      FALSE       TRUE
23      Thayer, Trygve      12/13/2003  1.00      Call In                                       FALSE      FALSE
33      Thayer, Trygve      12/14/2003  0.00      Continuance                      FALSE      FALSE
34      Thayer, Trygve      12/15/2003  0.00      Continuance                      FALSE      FALSE
                Thayer, Trygve          4/14/2003    -1           Award                                      FALSE       TRUE
27      Thayer, Trygve      4/17/2004        1.00      Call In                            FALSE      FALSE


To get this I first marked all records older than 1 year from the current date in [Occurrence Dropped] and [Occurrence Reference]

Next I looked in the records by employee that are not checked and looked for the most recent record where an award was given (I guess you could look for a -1).  Then from there I looked for a span of 4 months (I did not count the records with a value of 0 in the [Occurrence Value] field and found the first one on 8/11/2003 to 12/12/2003 so I added a record.  I then looked from the most recent which is now 12/12/2003.  I found the next set to be from 12/13/2003 to 4/14/2003 (It would not be 12/12/2003 because there was a record after that date.) So I added a record.  You would keep doing this until you get to the end of the record set.

Just checking in.

Hi ty,

Have parsed the new data into my table, not got much further yet.

Alan

Thanks for the response.  Did I correctly set up the table to show the fractional values?  I did not know if setting the field [Occurrence Value] to currency and the format to standard was correct but wanted to let you know that is how I did it.

That will be fine mate.

we could have used integer values 0,25,50,75,100 too. Same deal.
We could actually store 0,25,50,75,100 in the checkbox fields.
25,50,75,100  would return true, and 0 would return false therby adding another dimension to the yse/no fields.

Alan

2. Go through all records in tblOccurrence and find the ones that have a value of 0 in [Occurrence Value].  
                      In the found records mark the field [Occurrence Dropped]

seems easy enough.


3. Find all records by Employee in the current year (current year is 1 year back from the current date) and find the most recent record (by Date) with the [Occurrence Reference] marked and select all records after.  If nothing is found, keep the found records for the employee for 1 year back.
                  Search through the records for a 4 month span of values in the [Occurrence Value] field greater than 0]  
                         If nothing is found then do nothing.  
                         If one is found (searching from the oldest to the newest) then add a record with the appropriate
                         Employee, Ending date of the 4 month span , Value of  -1 , Type as Award, Comments
                         of "Attendance Award", Dropped as No, and Reference as Yes.


Part 3  seems a lot like what we already do except for the last bit.

Type as Award, Comments
                         of "Attendance Award", Dropped as No, and Reference as Yes.

Adding the value "Attendance Award" in the Type field.

Am I seeing this right or missing something?







Alan

It is very similar.

3 differences I see....

1. The current code does not overlook a record with a value of O when finding a 4 month span.

2. The code marks the ending record of the 4 month span as [Occurrence Reference] to remember when the last award was given.  That is a bit off as the span could be more than 4 months which would cheat the employee out of some days to be counted on the next 4 month span.

My thinking is to change the logic around so that a record is added on the ending date of the 4 month span and do away with the [Occurrence Reference] field.  The record then with the value of -1 would be the key to find the 4 month span.

3.  This is combined with my second difference above.  Current code drops the oldest record (regardless of value) to apply the award.  But as I have found out we need to drop a full value of 1 which the creating of a record should do.

Ok, thanks for that, will try to weave it into the fuctionality we have.

Alan

Alan,

2:30am here.  Will knock off for the night.  I will check in the morning to see if you have any more questions of me.  Thanks for being so patient and understanding with me.

Alan,

How is the weaving going?

Hi Ty,

sorry mate been caught up with some urgent work and family dramas.
will get on it as soon as I get a chance to catch my breath.

Alan

Thanks for the response.

Just came down to check tonight.  I guess its morning your time.  I told my daughter I was talking with someone in Australia.  Her eyes lit up with excitement.  I will be patient.

Hi Ty,

Yes it's 11:12 am here, beautiful sunny day here in Oz.
Hows things with you and yours?

Alan

Things are going well.  All systems at work are finally back to normal.  I have been attempting myself to work on this code but to no success.  I am amazed at how fast you guys turn this out.  I bought an Access book to try and understand the coding but its pretty confusing for me.  At the pace I am going I might figure it out next year. LOL

      . .
     ----  


Just peeking in.

ala Frosty......any luck in getting part 3 ?

alanWarren....know you are busy but haven't had any luck on my own.  It just blows up every time I try something.

Hi Ty,

I have not forgotten mate, I am chasing a deadline so that I can deliver a job before this coming monday, if I miss the deadline I will not be able to invoice for the job till next month. Currently 5:00pm thursday here, hope to look at your problem sometime this coming weekend. I had a look at it last night but was too tired to get my head around the complexities of the processes.

Sorry about the delay :(

Alan

OK thanks.  I will check back this weekend.

No, I gave up after I realized that the answer to #3 was hinging on the fractions which couldn't be supported by the elements. I get frustrated when the question "how do you add one plus one" turns into "how do you add one elephant to one idea and explain the meaning of god .. bonus marks for looking good in the process."

ala frosty.......Sorry a person needing help frustrates you so much.  If I had an HR manager that could make their mind up and I could translate the manual I might have been able to be more clear.  As you can see from the previous posts (alan gave you the link to) it has been a long process.  So far as of the last post here is the code I have.

What it doesn't do is

3 differences I see....

1. The current code does not overlook a record with a value of O when finding a 4 month span.

2. The code marks the ending record of the 4 month span as [Occurrence Reference] to remember when the last award was given.  That is a bit off as the span could be more than 4 months which would cheat the employee out of some days to be counted on the next 4 month span.

My thinking is to change the logic around so that a record is added on the ending date of the 4 month span and do away with the [Occurrence Reference] field.  The record then with the value of -1 would be the key to find the 4 month span.

3.  This is combined with my second difference above.  Current code drops the oldest record (regardless of value) to apply the award.  But as I have found out we need to drop a full value of 1 which the creating of a record should do.




Private Sub Command13_Click()
  On Error GoTo ReportError
 
  Dim strSQL As String
  Dim strWhere As String
  Dim strFilesChecked As String   ' InClause for sql where condition
                                  ' (records to exclude on next loop of active set)
 
  Dim recOccurrence As ADODB.Recordset
  Dim recEmployee As ADODB.Recordset
  Dim dNextNewestDate As Date
  Dim dNextOldestDate As Date
  Dim lNextOldestID As Long
  Dim lNextNewestID As Long
  Dim lRecordChecked As Long
 
  ' 1. -The update should check all records and mark the field
  ' [Occurrence Dropped] if the records are older than 1 year.
  strSQL = "UPDATE tblOccurrence SET tblOccurrence.[Occurrence Dropped] = True"
  strSQL = strSQL & " WHERE [Date] <=DateAdd('yyyy',-1,Now())"
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

'2. Check all records in the current year and starting with the earliest date
'   see if there is a span of more than 4 months

  Set recOccurrence = New ADODB.Recordset
  Set recEmployee = New ADODB.Recordset

  strSQL = "SELECT Distinct tblOccurrence.Employee FROM tblOccurrence"
  recEmployee.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  With recEmployee
    .MoveFirst
    Do While Not .BOF And Not .EOF
    '=====================   ' add this if block here
     If recOccurrence.State <> 0 Then
        recOccurrence.Close
     End If
    '=====================
     
      Do
        ' open the active set for this employee
        strSQL = "SELECT OccurrenceID, Employee, [Date], [Occurrence Dropped], [Occurrence Reference] FROM tblOccurrence"
        strSQL = strSQL & " WHERE Employee='" & .Fields("Employee")
        strSQL = strSQL & "' AND [Occurrence Dropped]=False AND [Occurrence Reference]=False"
        ' Exclude ID's in this active set that we have already checked
        If strFilesChecked <> "" Then
          strSQL = strSQL & " AND OccurrenceID Not IN(" & Left(strFilesChecked, Len(strFilesChecked) - 1) & ")"
        End If
       
        strSQL = strSQL & " Order By Date Asc"
        recOccurrence.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        With recOccurrence
          ' .MoveFirst                ' <-- aw 15-apr-2004 moved this down 2 lines
          If Not .BOF And Not .EOF Then
            .MoveFirst                ' <-- aw 15-apr-2004 modified positon of this line
            lNextOldestID = .Fields("OccurrenceID")
            lRecordChecked = lNextOldestID
            dNextOldestDate = Format(.Fields("Date"), "dd-mmm-yyyy")
            .MoveNext
           
            If .EOF Then: Exit Do
            lNextNewestID = .Fields("OccurrenceID")
            dNextNewestDate = Format(.Fields("Date"), "dd-mmm-yyyy")
            'If a span of 4 months is found
            '  and there are no records within the current year marked [Occurrence Reference]
            '  then mark the oldest record in the current year as dropped
            '  and mark the record the 4 month span was found.
            If dNextOldestDate <= DateAdd("m", -4, dNextNewestDate) Then
              ' a span of 4 months has been found
              '  then mark the oldest record in the current year as dropped
              '  and mark the record the 4 month span was found.
              '  Of the two records we are comparing, which record gets marked as
              '  Fields("Occurrence Reference") = true ????
              '  dNextOldestDate or dNextNewestDate  ????
             
              '  ==========================================
              '  Code to mark the oldest occurence, in current year, not marked as dropped,
              '  for this employee goes here.
              '
              '  note: Get the ID of oldest record not dropped for this Employee
              '        Then run some sql here to mark as dropped and re-populate the variable
              '  ==========================================
              strFilesChecked = strFilesChecked & .Fields("OccurrenceID") & ","
              Dim lOldestOffenceID As Long
              lOldestOffenceID = getOldestActiveOffenceID(.Fields("Employee"))
              Call doMarkOldestActiveOffenceID(lOldestOffenceID)
              Call doMarkOccurencReference(lNextNewestID)
             
            Else
              ' Add the ID of this record to strFilesChecked(List of records to exclude on next loop)
              strFilesChecked = strFilesChecked & lRecordChecked & ","
            End If
         
          Else     '<-- aw 15-apr-2004 Added the Else to get out of the inner loop if .BOF and .EOF
            .Close
            Exit Do
         
          End If
          .Close
        End With
      Loop
      strFilesChecked = ""
      .MoveNext
    Loop
  End With
 
ExitProcedure:
  On Error Resume Next
  Set recOccurrence = Nothing
  Set recEmployee = Nothing
  Exit Sub

ReportError:
  Dim msg As String
  msg = "Error in " & Me.Name & "Command11_Click():" _
    & vbCr & "Error number " & CStr(Err.Number) _
    & " was generated by " & Err.Source _
    & vbCr & Err.Description
  MsgBox msg, vbExclamation + vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
  Resume ExitProcedure
End Sub



Private Sub doMarkOccurencReference(lNextNewestID As Long)

  ' Note: aw 14-apr-2004
  ' Returns: nothing
  ' Called by: Form1_Command13_Click()
 
  On Error GoTo ExitProcedure
 
  Dim strSQL As String
  Dim strWhere As String
 
  strSQL = "UPDATE tblOccurrence SET [Occurrence Reference] = True"
  strSQL = strSQL & "  WHERE OccurrenceID =" & lNextNewestID
 
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
 
ExitProcedure:
  On Error Resume Next
  Exit Sub

ReportError:
  Dim msg As String
  msg = "Error in " & Me.Name & ".doMarkOccurencReference()" _
    & vbCr & "Error number " & CStr(Err.Number) _
    & " was generated by " & Err.Source _
    & vbCr & Err.Description
  MsgBox msg, vbExclamation + vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
  Resume ExitProcedure
 
End Sub




Private Sub doMarkOldestActiveOffenceID(lOldestOffenceID As Long)

  ' Note: aw 14-apr-2004
  ' Returns: nothing
  ' Called by: Form1_Command13_Click()
 
  On Error GoTo ExitProcedure
 
  Dim strSQL As String
  Dim strWhere As String
 
  strSQL = "UPDATE tblOccurrence SET [Occurrence Dropped] = True"
  strSQL = strSQL & "  WHERE OccurrenceID =" & lOldestOffenceID
 
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
 
ExitProcedure:
  On Error Resume Next
  Exit Sub

ReportError:
  Dim msg As String
  msg = "Error in " & Me.Name & ".doMarkOldestActiveOffenceID()" _
    & vbCr & "Error number " & CStr(Err.Number) _
    & " was generated by " & Err.Source _
    & vbCr & Err.Description
  MsgBox msg, vbExclamation + vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
  Resume ExitProcedure
 
End Sub


Private Function getOldestActiveOffenceID(empName As String) As Long

  On Error GoTo ExitProcedure
 
  Dim strSQL As String
  Dim strWhere As String
  Dim recOldestOffence As ADODB.Recordset
  Set recOldestOffence = New ADODB.Recordset
 
  ' Note: aw 14-apr-2004
  '       In the test data OccurenceID's were not consistent with field("Date")
  '       when an orderby clause was applied
  '       Also dont need to filter on WHERE [Date] <=DateAdd('yyyy',-1,Now())
  '       Because update of records matching this descript runs prior to this function
 
 
  strSQL = "SELECT OccurrenceID, Date FROM tblOccurrence"
  strSQL = strSQL & " WHERE Employee='" & empName
  strSQL = strSQL & "' AND [Occurrence Dropped]=False ORDER BY [Date]"
 
  recOldestOffence.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  With recOldestOffence
    If Not .BOF And Not .EOF Then
      .MoveFirst
      getOldestActiveOffenceID = .Fields("OccurrenceID").Value
      .Close
    End If
  End With
 
 
ExitProcedure:
  On Error Resume Next
  Set recOldestOffence = Nothing
  Exit Function

ReportError:
  Dim msg As String
  msg = "Error in " & Me.Name & ".getOldestActiveOffenceID()" _
    & vbCr & "Error number " & CStr(Err.Number) _
    & " was generated by " & Err.Source _
    & vbCr & Err.Description
  MsgBox msg, vbExclamation + vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
  Resume ExitProcedure
 

 
End Function

Hmm getting a very long Q and hard to understand for me what has been changed since the original question text.
Without reading all I would like to see or it's possible to get this problem described "functionally" before diving in the solution.

From the above question text I read:

Logic
1.  Go through all records in tblOccurrence and find the ones that are older than one year from the current date.  
                      In the found records mark the field [Occurrence Dropped].
==> Why ?
This as obviously a simple compare between the Date (bad name, would be better to call this OccurrenceDate) and the current date will give this information and no update is needed in the table.

2. Go through all records in tblOccurrence and find the ones that have a value of 0 in [Occurrence Value].  
                      In the found records mark the field [Occurrence Dropped]
==> What does the [Occurrence Value] field represent ?

3. Find all records by Employee in the current year (current year is 1 year back from the current date) and find the most recent record (by Date) with the [Occurrence Reference] marked and select all records after.  If nothing is found, keep the found records for the employee for 1 year back.
                  Search through the records for a 4 month span of values in the [Occurrence Value] field greater than 0]  
                         If nothing is found then do nothing.  
                         If one is found (searching from the oldest to the newest) then add a record with the appropriate
                         Employee, Ending date of the 4 month span , Value of  -1 , Type as Award, Comments
                         of "Attendance Award", Dropped as No, and Reference as Yes.

==> What does the [Occurrence Reference] field represent and when is it marked ?
What do you want to achieve with the insert of the new record.

Nic;o)

To describe functionally what I am needing to do is construct a database to keep track of employees attendance.  

If they are late they get an occurrence.  Occurrences are given in fractions.   .25  .5   .75  and 1  depending on how late they are.  If an employee accrues 4, and 6, occurrences in a 12 month period then they will be given a first and second warning.  If they get 8 then they are terminated.

Rewards of taking away 1 full occurrence are given if an employee has gone 4 months without an occurence (approved occurences which will be adjusted to a value of 0) should not be considered as a record.)  This reward can only be given 3 times in a 12 month period

My intensions for this is to be able to print 3 types of reports.  
1 Once that shows the employees attendance record for the current year with totals.  
2. One to look at when an employee has reached a warning or termination in which a record can be created that the warning was given.
3. One to look at employees who "ride the line" so to speak, so this report would look bac to years to see what their average is.  If they have carried above a 6 over 2 years there is now action for that as well.
 

Good description Thygve,

Indeed a complex situation. All's based on the OccurrencesDate field and besides that only whether or no an accrued 4 and/or 6 took place, all data can be extracted/concluded from the last year of Occurrences records for an attendee.

One thing not clear is with what frequency an accrued 4 or 6 is checked.
When you accrue daily there will be no problem, but when the frequency is once a week, you can have to issue a 4 and 6 warning in one go.

The constraint ">This reward can only be given 3 times in a 12 month period<" isn't really a constraint as 4 * 3 = 12 :-)
Looking into this further would even learn that a reward can only be given after a reported occurrence so basically 2 rewards will be possible within a year.

Also a problem when there's a reward of -1 when there's only an Occurrence of e.g. 0.5 present. Is the other 0.5 "carried on to a next one or not ?

When it's a "moving year" system, but also needs to be able to process over multiple years, I would probably create a function to do all processing from a given date and create a temp table with just a row per Occurrence and the calculated OccurrenceValue like:
Employee, OccurrenceDate, RunningSumOccurrenceValue
Thus you can create a report with an extra field to signal the 4/6/8 warning based on the RunningSumOccurrenceValue.

And a final question, when processing for multiple years, is the score resetted to zeror after the first year ?

Nic;o)

Thanks for your response.  I want to look into your questions further so I do not lead you in the wrong direction.  I would have responded earlier but after 3 years of using this Dell Laptop I have had a drive failure (new one being shipped) Lucky me I did a backup last night.  I had to wait until I got home before I could check EE.

Sorry to hear, but no problem, just post when ready.
Still trying to model a solution and to see al sides of it.
E.g. when someone has 4 occurrences with a 1 value like:
1/1/2003 1/4/2003 1/7/2003 12/31/2003
Then there is 12/31/2003 a warning class 4, but 1/1/2004 all is OK, this shows that the moment/frequency of running the report can be causing a kind of different results....

Nic;o)

I hope to get the information in the next few days.  This past weekend has found me upgrading our infrastructure to accomodate 5 additional facilities to connect VIA VPN's to our business system server.  Whew!!!

This is still very important to me!  I thank you for your patience.

Any progress Trygve ?

Nic;o)

I have been out of town for the past week taking care of setting up communications for a new facility to connect into our corporate office.  Sorry,  Had to bounce out there for a short time.  Thanks for keeping up with this as it is very important and I now have time to devote to getting this working.

To Start Off I have added below the Attendance Policy I have been given to work from in hopes to clear some of your questions.  I will also attempt to respond to the individual ones you asked above.  I am willing to change anything upon your suggestion as I know there are better ways of getting the information. I am a novice in a tight spot.  Thanks for your patience.  I haved worked hard in getting enough points in the areas of networking and pc's to get premium services.  I will bump up the points to whatever is needed in additional post to help me accomplish this task as a follow up to this question as I had not attained premium services at the time and this is well worth 1000 points to me and more.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your 1st comment........One thing not clear is with what frequency an accrued 4 or 6 is checked.  When you accrue daily there will be no problem, but when the frequency is once a week, you can have to issue a 4 and 6 warning in one go.

I understand your thoughts.  It is the intention to run this every day.  The manufacturing plant currently runs 24 hours a day 7 days a week but in off-peak times it is limited to 24 hours a day and 5 days a week.  I am sure that this will be ran every day but for this situation 1 time a week should be planned for.  I discussed this with the HR manager and the response was "Then the 4 and 6 in one go will be fine.  The employee is well aware of the guidelines.  My interest here is to capture proof."

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your second comment.......The constraint ">This reward can only be given 3 times in a 12 month period<" isn't really a constraint as 4 * 3 = 12 :-)  Looking into this further would even learn that a reward can only be given after a reported occurrence so basically 2 rewards will be possible within a year.

The HR manager says that there can be up to 3 "rewards" a year but to do that an employee would have to have perfect attendance for a complete year.  In my statements above it does appear that a reward can be given only after a reported occurrence but I feel that may be my inaccurate interpretation of the guidelines.  It states an employee can not go below 0 and a period of 4 months of perfect attendance is required to get the reward.  (I think maybe a bettter way to state it is the 4 month span should be calculated from the last reward or occurrence) Does this help?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your 3rd comment...........Also a problem when there's a reward of -1 when there's only an Occurrence of e.g. 0.5 present. Is the other 0.5 "carried on to a next one or not ?

Employees occurrence can not go below 0 so if an employee is given a reward with only a .5 then the reward would only take them to 0.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your 4th comment.......When it's a "moving year" system, but also needs to be able to process over multiple years, I would probably create a function to do all processing from a given date and create a temp table with just a row per Occurrence and the calculated OccurrenceValue like:
Employee, OccurrenceDate, RunningSumOccurrenceValue
Thus you can create a report with an extra field to signal the 4/6/8 warning based on the RunningSumOccurrenceValue.

That is fine with me.  Just tell me where to put it.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your 5th comment.......And a final question, when processing for multiple years, is the score resetted to zeror after the first year ?

The score is not reset to 0 after the first year.  It is continuous.  The only instance of needing to reset the values was a result of adding another shift of workers.  Setting the values to 0 was the result of union negotiations.   This has only happened 2 times in the 14 years I have been here





xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Attendance Guidelines for Hourly Employees

Purpose
To establish minimum attendance requirements for hourly employees, uniform procedures for maintaining attendance records, appropriate disciplinary actions for attendance policy violations and a means of advising employees of attendance problems that may jeopardize their continued employment.

Scope
This policy applies to all active, full-time, hourly employees of Vacumet, Morristown.

Responsibility
Human Resources is responsible for:

1.   Maintaining official attendance records

2.   Notifying supervisory personnel of all attendance issues
      subject to disciplinary action.  

The immediate supervisor of each hourly employee  is responsible for:

1.   Accurately reporting to Human Resources all absences,       tardies, and early departures on a daily basis

2.   Carrying out the appropriate disciplinary actions for                         attendance policy violations.

Attendance Requirements
All employees are expected to be at their work station at or prior to the start of each shift for which they are scheduled to work.  Employees who will be absent from work are required to notify Management (Production Manager, Process Technician, Maintenance Manager, or Maintenance Supervisor) at least one (1) hour prior to the start of their shift, but preferably as soon as possible.  Messages sent by another employee or left on voice mail are not acceptable.

Absenteeism
Absences from work will be considered in terms of “occurrences,” with the following number of occurrences for each type of absence.
 
1 occurrence for each period of absence from work. (consecutive scheduled work days)
For example:  An employee who misses Monday, Tuesday, and Saturday but is scheduled off Wednesday, Thursday, and Friday, will get one occurrence.  If the employee is absent, then works, and is absent again, the days are not consecutive and there will be two occurrences.

 (Requires a medical doctor's excuse, on a Company-supplied form, for absences longer than 3 days.)  .

¼  occurrence for each late arrival or early departure of less than one (1) hour.


½  occurrence for each late arrival or early departure of less than two (2) hours.

1 occurrence for late arrival or early departure greater than two hours

2 occurrences for each unexcused absence.

One occurrence will be removed with perfect attendance for four consecutive months.  Perfect attendance is defined as no tardies, early departures, or absences chargeable under the attendance policy.  Up to three occurrences may be removed per year.  The oldest occurrence will be removed under this system.  An employee cannot go below zero on the occurrence system.  Employees with “perfect attendance” will earn one extra personal day for the coming year.  (Note:  “Perfect” means the employee works a minimum of 100 days from April 1 to December 31, has no occurrences due to absence from work, and has no more than ½ occurrence for late arrivals or early departures.

Exceptions
All absences will be subject to these occurrences except those resulting from:

Holidays and vacation
Lack of work
An on-the-job injury
Disciplinary action (paid or unpaid)
Legal subpoena
Leaves of absence (as described in the Contract) for jury duty, funerals, or military duty
Leaves of absence for official Union business when notified three (3) days in advance.
Company approved leaves of absence for medical, personal or family leave purposes
Early departure, late arrival or absence for severe weather conditions. (At the Company’s discretion)


Unexcused Absences
An unexcused absence will be charged whenever an employee is:
1.  Absent from work without providing at least a one-hour notice prior to the start of the shift.
2.  Late for work without contacting management within two hours of the start of a shift.
Note:  An unexcused absence will not be charged in the event of an independently verifiable medical emergency.


Corrective Action
Excessive absenteeism will result in the following disciplinary action:

Step 1:  A written reprimand for:
            ---A total of four (4) occurrences during a twelve (12) month period
            ---Five occurrences at the end of each of four (4) consecutive calendar quarters

Step 2:  A final warning for:
            ---A total of six (6) occurrences during a twelve (12) month period
            ---Five (5) occurrences at the end of each  of  six (6) consecutive calendar quarters
            ---One (1) unexcused absence during a twelve (12) month period

Step 3:  Termination for:
             ---A total of eight (8) occurrences during a twelve (12) month period(twelve (12) hour shift schedule)
        ---A total of nine (9) occurrences during a twelve (12) month period(eight hour (8)shift schedule)
             ---Five (5) occurrences at the end of each of eight (8) consecutive calendar quarters
             ---Two (2) unexcused absences during a twelve (12) month period

Hmm, nasty one :-)

I already have problems with:
            ---A total of four (4) occurrences during a twelve (12) month period
            ---Five occurrences at the end of each of four (4) consecutive calendar quarters
            ---One (1) unexcused absence during a twelve (12) month periodLike

1) Is this monthly based and if so do we need to ignore the occurrences of the not yet finished month ?
2) The "Five occurrences" can be pointing to 1/4, 1/2, 1 and 2 recorded values in an occurrence or just the summed occurrence value.
3)  Are the "four (4) consecutive calendar quarters" based on the quarters starting 1/1, 1/4, 1/7 and 1/10 or also "floating"
4) The unexcused absence is obviously an occurrence with a value of 2, but does the "perfect attendance for four consecutive months" correct this or not ?

Nic;o)

I will discuss this with the HR manager and get you a reply.  It will probably be 2 days before I can speak with him.  I do not want to give you inaccurate information

Sorry I have not been back but have had a death in the family.  I have taalked with the HR Manager and will get back to you on Wednesday.

No problem, but I'll be off till sunday, so it looks like we have a long lasting Q here :-)

Nic;o)

I will make it worth your efforts.  Thanks for your patience.

I'm back :-)

Nic;o)

Ok,

I will start back in where you asked some questions above and I said I would talk to the HR manager.  

1. This is not monthly based.  It is ongoing.

2.  Calendar quarter are as follows.  Jan-March, April-June, July-September, October-December

3. Floating

4. The 4 consecutive months of good attendance does not reverse the 2 it only takes away 1.

Just checking in.

Good thing, I'm still trying to find some time as it'll take atleast some hours to get all  things straightened out.
I get the feeling that the rules are not covering the full 100% of the cases that are possible and that's a very time consuming operation. But be sure I have this on my list !

Nic;o)

Thanks,

Since this has gone on for quite some time I will review everything with the HR director and try to get a simplified version we can work from.

Basing the data e.g. on fixed months or quarters would really simplify the matter a lot. The fact that on the last day of the month one could get a warning that's "vanished" running the algoritm the next day is really combersome...

Nic;o)

I am now on vacation but since I have no work duties I now will have time to work on this in the evenings.  Before the night is over I will define what is needed.  This is too complicated and too many exceptions.  I will simplify the task as much as I can and the HR assistant will do the rest.  This will be better than what they have now which is a written log.  I will post back withing the next 6 hours.  I want some time to get back into it and try to clearly define the objective.

OK, still listening and wanting to help, but on the European timeschedule :-)
One way to help structuring your mind might be to draw a time line and above that you can mark the absence events. Now by creating a bottomline with the cumulative number of illegal absences and the "good behaviour" corrections you can get an idea of the different situations possible.

Nic;o)

Well vacation ended up being just that.  The wife had a long to-do list.  I never had a chance.  I am now back and have scheduled to meet with the HR manager as my boss has instructed me that this item has gone on long enough and if at all possible I need to complete this week.  I will send the information in the morning.  It is 12:15am here and time to get some sleep.  Thanks for hanging in there as the experts-exchange is my only hope of completing this task as I am too much a novice to complete on my own.

Just got the neede information from the HR manager after being away from this for quite some time.

The fields in the table called tblOccurrence are as follows:
OccurrenceID   (AutoNumber)
Employee (Text)----------------------To record the employees name
Occurrence Date  (Date/Time)-------To record the Date
Occurrence Value (Number)----------To record the value of the Occurrence
Occurrence Type (Text)---------------To record what the Occurrence was for
Occurrence Comments (Text)---------To record additional comments
Occurrence Dropped (Yes/No)---------To determine when a record is Dropped
Occurrence Reference (Yes/No)-------To determine when a record is rewarded

Information I need to pull out

1. A weekly report by employee that shows occurrences for the week and the total occurrences for each day.  Total occurrences are calculated from the current day back 1 year.
2. A quarterly report that shows for the past 8 quarters what an employees total occurrences were at each quarter.
3. A way to add a record with a value of -1 when 4 months perfect attendance is achieved. The 4 months of good attendance then starts again after the date of the record of perfect attendance.
4. a report by employee that shows occurrences for 1 year back based on occurrence type.

Hmm, looks better now there are fixed year, quarter, week and day periods.
I would probably drop the fields
Occurrence Dropped (Yes/No)---------To determine when a record is Dropped
Occurrence Reference (Yes/No)-------To determine when a record is rewarded
as we can calculate them "on the fly" when doing this for a quarter or a year.

We need to process the rows for an employee that are present in a period to calculate the "score", but as the quarters will "scroll" trough the calendar we'll have to recalculate them each time using the startdate that's 2 years back.

Guess we now can standardize on the data from the last weekday as the enddate and two years before as the startdate.

Nic;o)

OK,

Now that the reports are defined how do we update the database for an employee who has had 4 months of good attendance and reward them with a credit value of -1

We can only reward them when we know the "history".
To calculate the "attendance score" we start with the first occurrence that's still valid (I guess those older as 2 years are expired).
When there are no occurrences the score is 0
When there are multiple scores just keep track by processing each row and applying the rules when processing them.
Each attendance failure will be added in the Overall Counter and a good attendance can be placed in a separate counter, thus enabling you to calculate the needed value but also show it has been applied.
Do you know how to write a record processing loop ?

Nic;o)

I unfortunately am a novice at writing code. I am now cleared to give this my full attention.

Ok,

I will give it a try.  I have gone and purchased an access book to help me fill in the gaps.

At this point I have the table called tblOccurrence with the following fields.

OccurrenceID   (AutoNumber)
Employee (Text)----------------------To record the employees name
Occurrence Date  (Date/Time)-------To record the Date
Occurrence Value (Number)----------To record the value of the Occurrence
Occurrence Type (Text)---------------To record what the Occurrence was for
Occurrence Comments (Text)---------To record additional comments

The first report I am working on is a weekly report by employee that shows occurrences for the week and the total occurrences for the year.  Total occurrences are calculated from the current day back 1 year.  I will then proceed to the next reports.  I am thinking the loop above will be used in the following reports.  Right?

The occurrences aren't the "problem", the correction for "good behaviour" is as it's depending on one or more occurrences and when you start your period.
I would use the loop to "generate" temporary "good behaviour" occurrences with a negative value and a date corresponding when the "good behaviour" was earned. As this is depending on one or more previous occurrences we need to make sure it's calculated correctly. Do try to write the mechanism before starting.

Nic;o)

To show progress to my superiors I am doing the reports.  I will then come back to working on the update for good behavior.  If you feel this is the wrong approach I will proceed with your recommendations.  I know you are busy but this has now become a hot item for me again.  The current report I am working on is below.

https://www.experts-exchange.com/questions/21114603/Report-to-count-up-total-occurrences-by-Employee-from-current-quarter-back-7-quarters.html

I object to splitting the points.  Ala_Frosty was offending to say the least.  Alanwarren was helpful in prior posts but failed to provide me with the code I am needing help with.  Nico5038 has been helping me the most and I have been trying to write the code myself with intensions of posting for additional assistance.  I do not object to closing the post but recommend Nico5038 receive all the points.

What happens is entirely up to you, I am only volunteering to help suggest how to close old questions according to their guidlines.

I personally agree with your assessment of AlaFrosty's approach but you did say s/he got 2 out of 3 correct.

If you would like Nico to recieve all the points then please accept one of his posts.

The Moderators who actually close questions read the suggestions and any posts after that before taking any action. You can leave a post here saying please leave it open.

Steve

Kudos to Nico and the points too,
 I am sorry I abandoned this thread,
 was going through some tough times.

Appreciate the work you are doing here Steve.

Take care...


Alan