2 update Querys that conflict with each other

<Check by Employee if there is a period of more than 4 months.  If so mark the oldest record as dropped.
>

What if you have more than 2 records? How many will be dropped? Just the oldest one?

If you have records like this
Employee             Date
aaa                     5/01/2003
aaa                     8/01/2003
aaa                     9/01/2003

What should be done?

Just the oldest one.

For each 4 months an employee does not get an occurrence their oldest one drops off.  They have to have 4 more months before another one can drop off.

Is there a possibility that an employee will have an occurrence like this
Employee             Date
aaa                     1/01/2003
aaa                     5/01/2003
aaa                     9/01/2003

What is SQL for the two current querys that you have?

Andrew

I'm guessing that the first one is like this:

UPDATE tblOccurrence SET [Occurrence Dropped] = True WHERE DateDiff("d",[Date],Now) >= 365;

CAPRICORN1.....
I intend this update will run daily so I do not expect to have 2 instances of more than 4 months but I do expect  and instance of 1/1/2003, 1/10/2003, 5/10/2003,9/10/2003.  Where if the query runs on 5/10/2003 it will mark 1/1/2003 and then if it runs 5/12/2003 it will not mark 1/10/2003 because 4 months have not passed since it last marked 1/1/2003.  I am beginning to think I need another field in the table to be able to determine when the last one was marked.  Don't know for sure but that's why I am depending the experts here.

NEXUSNATION ......
Querys I am trying to use are below


Query to drop oldest record if a 4 month period has past

UPDATE tblOccurrence SET tblOccurrence.[Occurrence Dropped] = 1
WHERE (((tblOccurrence.OccurrenceID) In (SELECT  m.OccurrenceId    FROM tblOccurrence AS m,         [SELECT Max(datediff("m", tblOccurrence.Date, now))  AS MaxD,                 Min(tblOccurrence.Date) AS MinOfOCCDate,                 tblOccurrence.Employee            FROM tblOccurrence           group by employee]. AS v   WHERE m.Employee = v.Employee  and m.Date = MinOfOCCDate     and v.MaxD >= 4))) AND tblOccurrence.[Occurrence Dropped] = 0;


Query to drop all records older than 1 year

UPDATE tblOccurrence SET tblOccurrence.[Occurrence Dropped] = -1
WHERE tblOccurrence.Date<DateAdd("Y",-365,Date());

aye, you're gonna need another field or table that has:  UserID | LastMarked   so u can check when the last one was marked, or else it will remove everything that is 4 months +

<I am beginning to think I need another field in the table to be able to determine when the last one was marked.>

This is the only way to satisfy this statement

{Where if the query runs on 5/10/2003 it will mark 1/1/2003 and then if it runs 5/12/2003 it will not mark 1/10/2003 because 4 months have not passed since it last marked 1/1/2003}

I will add a field called [Occurrence Reference] and make it a check box.  With this in mind what should my code look like?

{Where if the query runs on 5/10/2003 it will mark 1/1/2003 and then if it runs 5/12/2003 it will not mark 1/10/2003 because 4 months have not passed since it last marked 1/1/2003}

You need more than a check box; A field indicating when it was marked.{date field}

I will add what you tell me to.

Hi tthayer

You might want to back up your table first but give this a go if you please:

UPDATE tblOccurrence AS O1
  SET O1.[Occurrence Dropped] = True
WHERE (((O1.Date)<=DateAdd("yyyy",-1,Now())))
  OR (((O1.OccurrenceID) In (
                                               SELECT O2.OccurrenceID FROM tblOccurrence AS O2
                                               WHERE (((O2.Employee)=O1.Employee)
                                                 AND ((O2.[Occurrence Dropped])=False))
                                               GROUP BY O2.OccurrenceID, O2.[Date]
                                               HAVING (((O2.OccurrenceID)<O1.OccurrenceID)
                                                 AND ((O2.[Date])>=DateAdd("m",-4,O1.[Date])))))
                                              )




Alan

I tried it and it  did mark all the records that were earlier than 1 year but it did not mark the record within one year that had a 4 month time span.

I know this is confusing but if I were to add another field called [Occurrence Reference] would it be easier?  I have consulted with the employment manager and the record that needs to be dropped is 4 months from the current date provided the last one dropped has been 4 months.  I will try to map this out below.

This update will be ran daily.

1. -The update should check all records and mark the field [Occurrence Dropped] if the records are older than 1 year.

2. -It should then check all records BY EMPLOYEE from the current date back 4 months.  
         -If records are found do nothing.
         -If   NO   records are found it should find the last record (can be older than 1 Year) of the employee
          that is marked [Occurrence Reference]
                 -If the date of the [Occurrence Reference] is more than 4 months from the current date then mark
                   [Occurrence Dropped] and [Occurrence Reference] of the oldest record that is not marked as
                   [Occurrence Dropped].
                 -If    NO  records are found as [Occurrence Reference] then mark[Occurrence Dropped] and
                   [Occurrence Reference] of the oldest record that is not marked as [Occurrence Dropped].

Table name:    tblOccurrence

Fields:            OccurrenceID                 AutoField
                     Employee                      Text
                     Date                              Date/Time
                     [Occurrence value]         long integer
                     [Occurrence Type]          text
                     [Occurrence Comments] Text
                     [Occurrence Dropped]     Check Box
                     [Occurrence Reference]  Check Box

thayer,

I don't think that what you want could be done on a plain query
I created the codes to do that. Just run the Update for 1 year old records separately.

Create a copy of your tblOccurrence and just for testing, add a field  MarkedDate  Date/Time type
rename your Date field as oDate <<<<<<                                     ^^^^^^^    ^^^^^^
Avoid using Date as a name of a field, this is one of the reserved word for Access and VBA.

Copy this codes on a command button cmdUpdate


Private Sub cmdUpdate_Click()
  Dim db As Database, rst As Recordset
  Dim strRecord As Integer, strRecord1 As String, strRecord2 As String
  Dim strRecord3 As String, strRecord4 As String
  Dim sSQL As String, sSQL1 As String, sSQL2 As String
  Set db = CurrentDb()

  Set rst = db.OpenRecordset("tblOccurrence")
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strRecord = rst.Fields("OccurrenceID")
      strRecord1 = rst.Fields("Employee")
      strRecord2 = rst.Fields("oDate")
      strRecord3 = rst.Fields("Occurrence Dropped")
      strRecord4 = Nz(rst.Fields("MarkedDate"), "")
    If strRecord3 = True Then GoTo NextRec
    If (strRecord4) <> "" Then
        If DateDiff("M", strRecord4, Date) < 4 Then GoTo NextRec
    End If
      If DateDiff("M", strRecord2, Date) >= 4 Then
            sSQL = "Update [tblOccurrence] Set "
            sSQL = sSQL & "tblOccurrence.[Occurrence Dropped] = -1, "
            sSQL = sSQL & "tblOccurrence.MarkedDate= Date() "
            sSQL = sSQL & "Where (tblOccurrence.OccurrenceID)= " & strRecord
        db.Execute (sSQL)
            sSQL1 = "Update [tblOccurrence] Set  "
            sSQL1 = sSQL1 & "tblOccurrence.MarkedDate= Date() "
            sSQL1 = sSQL1 & "Where (tblOccurrence.[Occurrence Dropped])= 0 "
            sSQL1 = sSQL1 & "And (tblOccurrence.Employee)= '" & strRecord1 & "'"
        db.Execute (sSQL1)
      End If
NextRec:
      rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
  End If
End Sub


Note: All Employee record that have an Occurrence Dropped  check will have a current date on the field MarkedDate,
         but not a checked on Occurrence Dropped if it is not due for marking.

Hi  tthayer,

Tend to agree with Capricorn1, that this may be beyond the scope of one jet query, could probably do it ms SQL Server by fetching a cursor and looping, but that is just the same as doing it with VBA looping.

Tested this it runs ok on my machine...Anyway fingers crossed here goes...

Private Sub cmdDoItNow_Click()

'1. -The update should check all records and mark the field [Occurrence Dropped] if the records are older than 1 year.
'
'2. -It should then check all records BY EMPLOYEE from the current date back 4 months.
'         -If records are found do nothing.
'         -If   NO   records are found it should find the last record (can be older than 1 Year) of the employee
'          that is marked [Occurrence Reference]
'                 -If the date of the [Occurrence Reference] is more than 4 months from the current date then mark
'                   [Occurrence Dropped] and [Occurrence Reference] of the oldest record that is not marked as
'                   [Occurrence Dropped].
'                 -If    NO  records are found as [Occurrence Reference] then mark[Occurrence Dropped] and
'                   [Occurrence Reference] of the oldest record that is not marked as [Occurrence Dropped].

  Dim strSQL As String
  Dim strWhere As String
  Dim bolFound As Boolean
  Dim recOccurrence As ADODB.Recordset
  Dim recEmployee As ADODB.Recordset
  Dim lOccurrenceID 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 (((tblOccurrence.Date)<=DateAdd('yyyy',-1,Now())))"
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

'2. -It should then check all records BY EMPLOYEE from the current date back 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
    If recEmployee.State <> 0 Then
      'do stuff
      With recEmployee
        Do While Not .BOF And Not .EOF
          strWhere = "Employee='" & .Fields(0) & "' AND [Date]>=DateAdd('m',-4,Date())"
          bolFound = Nz(DCount("OccurrenceID", "tblOccurrence", strWhere), 0)
          If bolFound Then
            ' -If records are found do nothing.
          Else
            ' -If   NO   records are found it should find the last record
            ' (can be older than 1 Year) of the employee
            ' that is marked [Occurrence Reference]
            ' -If the date of the [Occurrence Reference] is more than 4 months
            ' from the current date then mark [Occurrence Dropped] and
            ' [Occurrence Reference] of the oldest record that is not marked as
            ' [Occurrence Dropped].
            strWhere = "Employee='" & .Fields(0)
            strWhere = strWhere & "' AND [Occurrence Reference] <> 0  AND [Date]< DateAdd('m',-4,Date())"
            lOccurrenceID = Nz(DMax("OccurrenceID", "tblOccurrence", strWhere), 0)
            If lOccurrenceID <> 0 Then
              ' mark [Occurrence Dropped]
              strSQL = "SELECT tblOccurrence.* FROM tblOccurrence WHERE (((OccurrenceID)=" & lOccurrenceID & "))"
              recOccurrence.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
              If recOccurrence.State <> 0 Then
                'Do stuff
                recOccurrence.Fields("Occurrence Dropped").Value = True
                recOccurrence.Update
                recOccurrence.Close
              Else
                MsgBox "Failed to open recOccurrence recordset", vbCritical
              End If
              ' and [Occurrence Reference] of the oldest record
              ' that is not marked as [Occurrence Dropped].
              strWhere = "Employee='" & .Fields(0)
              strWhere = strWhere & "' AND [Occurrence Reference] = 0"
              lOccurrenceID = Nz(DMin("OccurrenceID", "tblOccurrence", strWhere), 0)
              If lOccurrenceID <> 0 Then
                strSQL = "SELECT tblOccurrence.* FROM tblOccurrence WHERE (((OccurrenceID)=" & lOccurrenceID & "))"
                recOccurrence.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
                If recOccurrence.State <> 0 Then
                  'Do stuff
                  recOccurrence.Fields("Occurrence Reference").Value = True
                  recOccurrence.Update
                  recOccurrence.Close
                Else
                  MsgBox "Failed to open recOccurrence recordset", vbCritical
                End If
              End If
            End If
          End If
          .MoveNext
        Loop
        .Close
      End With
    Else
      MsgBox "Failed to open recEmployee recordset", vbCritical
    End If
 
 On Error Resume Next
 Set recEmployee = Nothing
 Set recOccurrence = Nothing
End Sub


hth

Alan :)

WOW you all are GREAT!!!  I will open up another case and add 500 more points and reward it to you as well as this is well worth it to me.  This is close.

I got it to work one time with allenWarren's solution but I tried this scenerio and it does not seem to work. I tested this on 4/11/2003.  Below is the data I have in my table:  I expected  the record on 5/1/2003 to be marked in both the [Occurrence Dropped] and [Occurrence Reference] fields.   Based on the requirements (which I probably did not communicate well) There are no records from 4/11/2004 back 4 months.  Therefore, In checking the date of the most recent [Occurrence Reference] I find one on 1/2/1998.  Since the 1/2/1998 record is older than 4 months I go to the oldest record that is not marked [Occurrence Dropped] and mark that record in both the [Occurrence Dropped] and [Occurrence Reference] fields.


10      Thayer, Trygve      1/2/1998      1      Call In            TRUE      FALSE
11      Thayer, Trygve      1/2/1999      1      Call In            TRUE      TRUE
12      Thayer, Trygve      1/2/2000      2      Call In            TRUE      FALSE
6      Thayer, Trygve      1/1/2001      2      Call In      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      Call In       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      Call In      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      Call In            TRUE      FALSE
1      Thayer, Trygve      4/10/2003      2      Call In  yep he did it.      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      6/1/2003      1      Over 2             FALSE      FALSE
23      Thayer, Trygve      11/1/2003      2      Call In            FALSE      FALSE
26      Thayer, Trygve      4/11/2004      1      Over 2             FALSE      FALSE

G'Day mate,

I imported your data and ran it on my machine

'2. -It should then check all records BY EMPLOYEE from the current date back 4 months.

' This line finds the only record that meets the criteria for point 2
'  all the others fall outside the 4 month criteria
  bolFound = Nz(DCount("OccurrenceID", "tblOccurrence", strWhere), 0)

' This record causes bolFound to return true
  26     Thayer, Trygve     4/11/2004     1     Over 2           FALSE     FALSE
- Action to take if records found...
- If records are found do nothing.


Am I missing something freind?

Alan :)

tthayer,
Did you try my suggestion?
Try it with the real data (copy of tblOccurrence)  Codes take accounts of the Employee name.

Tried this on the data you provided.

Private Sub cmdUpdate_Click()
  Dim db As Database, rst As Recordset
  Dim strRecord As Integer, strRecord1 As String, strRecord2 As String
  Dim strRecord3 As String, strRecord4 As String
  Dim sSQL As String, sSQL1 As String, sSQL2 As String
  Set db = CurrentDb()

 '--------- this part marked all records older than  1 year
sSQL2 = "Update tblOccurrence Set tblOccurrence.[Occurrence Dropped]=-1 "
sSQL2 = sSQL2 & "Where ((tblOccurrence.oDate)<=DateAdd('yyyy',-1,Now()))"
db.Execute sSQL2


  Set rst = db.OpenRecordset("tblOccurrence")
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strRecord = rst.Fields("OccurrenceID")
      strRecord1 = rst.Fields("Employee")
      strRecord2 = rst.Fields("oDate")
      strRecord3 = rst.Fields("Occurrence Dropped")
      strRecord4 = Nz(rst.Fields("MarkedDate"), "")
    If strRecord3 = True Then GoTo NextRec
    If (strRecord4) <> "" Then
        If DateDiff("M", strRecord4, Date) < 4 Then GoTo NextRec
    End If
      If DateDiff("M", strRecord2, Date) >= 4 Then
            sSQL = "Update [tblOccurrence] Set "
            sSQL = sSQL & "tblOccurrence.[Occurrence Dropped] = -1, "
            sSQL = sSQL & "tblOccurrence.MarkedDate= Date() "
            sSQL = sSQL & "Where (tblOccurrence.OccurrenceID)= " & strRecord
        db.Execute (sSQL)
            sSQL1 = "Update [tblOccurrence] Set  "
            sSQL1 = sSQL1 & "tblOccurrence.MarkedDate= Date() "
            sSQL1 = sSQL1 & "Where (tblOccurrence.[Occurrence Dropped])= 0 "
            sSQL1 = sSQL1 & "And (tblOccurrence.Employee)= '" & strRecord1 & "'"
        db.Execute (sSQL1)
      End If
NextRec:
      rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
  End If
End Sub

To Capricorn.....I changed date to oDAte and put your code on a command button.  I got a compile error saying user defined type not defined.  on this line    Dim db As Database, rst As Recordset

To allenwarren.....OK I removed the 4/11/2004 (my mistake. you were right) and the update did update all records that were older than 1 year as [Occurrence Dropped]  It then  marked  4/10/2003  as [Occurrence Reference] as well.  It needs to update 4/11/2003 as [Occurrence Refecence] instead of 4/11/2003.  The reason is 4/10/2003 is already "off the books" because it is older than one year.  So I guess my logic should say mark [Occurrence Reference] of the oldest record within the active year.  I apologise for not communicating that properly.  In hopes to clarify here is the information from the employees handbook.

---------------------------------------------------------------------------------------------------------------------------------
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

Add to your References
Microsoft DAO x.xx Object Library

Then try again
Works perfectly ok here with me.

G'Day mate,

I gotta crash out in a couple of minutes, 2:36am here, just about all coded out...

              ' and [Occurrence Reference] of the oldest record
              ' that is not marked as [Occurrence Dropped].
              strWhere = "Employee='" & .Fields(0)
              strWhere = strWhere & "' AND [Occurrence Reference] = 0"
              ' by adding this line it should get the id of first record matching criteria within the 1 year range.
               strWhere = strWhere & "' AND [Date]> DateAdd('yyyy',-1,Now())"        ' Add this line,  I think :)

              lOccurrenceID = Nz(DMin("OccurrenceID", "tblOccurrence", strWhere), 0)

Hopefully

Alan zzz

ps... you guys are heaps tough on your employees <yikes>

Alanwarren......

Added the line you suggested then clicked the command button and got a compile error on the line below (indicated with ***)  The error says it is a Syntax error (Missing Operator) in expression.  I too agree the rules are tough but it is from 10 years of an ongoing compromise between company and Union.
              ' and [Occurrence Reference] of the oldest record
              ' that is not marked as [Occurrence Dropped].
              strWhere = "Employee='" & .Fields(0)
              strWhere = strWhere & "' AND [Occurrence Reference] = 0"
              strWhere = strWhere & "' AND [Date]> DateAdd('yyyy',-1,Now())"
      ***  lOccurrenceID = Nz(DMin("OccurrenceID", "tblOccurrence", strWhere), 0)
----------------------------------------------------------------------------------------------------------------------
Capricorn.....How do I?   Sorry I do not understand your directions.

Add to your References
Microsoft DAO x.xx Object Library

allenwarren.....looking forward to hearing from you.

Capricorn.......I found how to add the library but do not know which one to add.  I have  2.5/3.51 compatibility, 3.0, 3.51, and 3.6.  I will try your code as soon as I know which one to add.

oops :(

sorry error in this line:
  strWhere = strWhere & "' AND [Date]> DateAdd('yyyy',-1,Now())"

should be:
   strWhere = strWhere & " AND [Date]> DateAdd('yyyy',-1,Now())"

Hi,

Re: DAO reference
Add the latest one 3.6

Alan

Alanwarren.....

It did properly mark the [Occurrence Reference] field for 5/1/2003  but did not mark the [Occurrence Dropped] field as well.

Thank you very much in advance.

Hi mate,

my data now looks like this:

OccurrenceID      Employee      Date      Occurrence value      Occurrence Type      Occurrence Comments      Occurrence Dropped      Occurrence Reference
37948961      Thayer, Trygve      02-Jan-1998      1      Call In            Yes      No
37948962      Thayer, Trygve      02-Jan-1999      1      Call In            Yes      Yes
37948963      Thayer, Trygve      02-Jan-2000      2      Call In            Yes      No
37948964      Thayer, Trygve      01-Jan-2001      2      Call In      tttt      Yes      No
37948965      Thayer, Trygve      01-Jan-2001      2      Call In      eeee      Yes      No
37948966      Thayer, Trygve      02-Jan-2001      1      Call In            Yes      No
37948967      Thayer, Trygve      01-Jan-2002      1      Call In      wwww      Yes      No
37948968      Thayer, Trygve      02-Jan-2002      1      Call In            Yes      No
37948969      Thayer, Trygve      30-Mar-2002      2      Call In            Yes      No
37948970      Thayer, Trygve      01-Jan-2003      1      Call In      rrrr      Yes      No
37948971      Thayer, Trygve      01-Feb-2003      1      Call In            Yes      No
37948972      Thayer, Trygve      01-Mar-2003      1      Call In            Yes      No
37948973      Thayer, Trygve      01-Apr-2003      1      Call In            Yes      No
37948974      Thayer, Trygve      10-Apr-2003      2      Call In      yep he did it.      Yes      No
37948975      Thayer, Trygve      11-Apr-2003      1      Call In            Yes      No
37948976      Thayer, Trygve      01-May-2003      2      Over 2            Yes      Yes
37948977      Thayer, Trygve      01-Jul-2003      1      Over 2            No      No
37948978      Thayer, Trygve      01-Nov-2003      2      Call In            No      No


Alan

AlanWarren.......

It is now marking the correct records.  I think it works but wanted to run this by you.  I noticed The first time I ran the code it marked the correct fields.  The second time I ran the code it marked the next record 01-jul-2003 in both the [Occurrence Dropped] and [Occurrence Reference] field.  Since I am working with data in the past I want to make sure that the [Occurrence Dropped] and [Occurrence Reference] field will only be marked if it has been 4 months since the most recent [Occurrence Reference] field in the current year.  If an [Occurrence Dropped] and[Occurrence Reference] field was marked in 01-May-2003 then another record can not be marked [Occurrence Dropped] and [Occurrence Reference] until 4 months hav past since 01-May-2003.

I am Going to reward you with the points as the scope of the question has changed.  I appreciate your patience.  As promised I will open up another post to continue this process and hope you will continue as well.  

Hi mate,

thanks for the points.

Yes I noticed that too, think we can sort something out, I just gotta run a couple of errands, might be an hour or so before I can get back to it, will look for your post.

Alan

<If an [Occurrence Dropped] and[Occurrence Reference] field was marked in 01-May-2003 then another record can not be marked [Occurrence Dropped] and [Occurrence Reference] until 4 months hav past since 01-May-2003.>

This is the reason why you need another field to put the date when the record was marked.