Solved

Employee Attendance VB Code to Drop records for each 4 months of good attendance.

Posted on 2004-04-11
47
605 Views
Last Modified: 2008-01-16
I have a database that keeps track of employees attendance.  The code properly marks all records older than 1 year and then marks the first unmarked record in the current year if there have been no records entered for the past 4 months.  What I need it to do is only mark a record if it has been 4 months.  Please see the following to get the information   http://www.experts-exchange.com/Databases/MS_Access/Q_20947819.html
0
Comment
Question by:tthayer
  • 22
  • 18
  • 7
47 Comments
 
LVL 3

Author Comment

by:tthayer
ID: 10803658
It is currently 1:00am here so will have to tuen in.  I will check back in 9 hours.  Thanks to all in advance.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10803760
Hi mate,

re: What I need it to do is only mark a record if it has been 4 months.

thinking you mean:  
What I need it to do is only mark a record if it has been 4 months since the previous occurence for that employee.

So once four months are up he/she is off the hook for that one. yes?

Alan
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10804795
tthayer,
Did you try my codes from the previous Q?
0
 
LVL 3

Author Comment

by:tthayer
ID: 10804797
To be more specific  I mean

What I need it to do is only mark a record if it has been 4 months since the previous record marked [Occurrence Reference] for that employee.

The employee is off the hook at that point.  They will have to work another 4 months before being able to drop another. They can only drop up to 3 records per year.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10804805
hi Mate,

that wasn't 9 hours, have you had your weetbix yet?

I'm onto it now boss. :)

Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10804869
Hi

this is where we are up to:

37948976     Thayer, Trygve     01-May-2003     2     Over 2          Yes     Yes   '<-- last one marked
37948977     Thayer, Trygve     01-Jul-2003     1     Over 2          No     No   '<-- this qualifies I think, 4 mth to 1-nov-03
37948978     Thayer, Trygve     01-Nov-2003     2     Call In          No     No

does you think that 37948977   should be marked    yes   yes         ????

Alan
0
 
LVL 3

Author Comment

by:tthayer
ID: 10805070
Went and talked to our HR manager and he put it in a different perspective.  So lets see if this makes sence.  Records are either active  or inactive.  In the tblOccurrence Active on inactive is represented by the [Occurrence Dropped] field.  This one works correctly as records are marked (meaning inactive) if they are 1 year old.

The second part which is very close is where I feel I am having trouble getting the logic right.  As you have seen the documentation is quite complex.

There is a decision process here.

If
   Within the active set if there are no records marked [Occurrence Reference] and there has been a 4 month span
   of no records from the current date then the oldest record within the active set needs to be marked
   [Occurrence Dropped] and [Occurrence Reference]

ELSE
   Within the active set if there is a record marked [Occurrence Reference] and there has been a 4 month span of
   no records from the current date then the oldest record within the active set can only be marked if it is 4 months
   from the last [Occurrence Reference Field]
END
0
 
LVL 3

Author Comment

by:tthayer
ID: 10805210
Capricorn.......

I added the DAO and inserted your code from the previous Q.  I got a type mismatch 13 error on the following line.

Set rst = db.OpenRecordset("tblOccurrence")
0
 
LVL 3

Author Comment

by:tthayer
ID: 10805974
alanwarren.....Was the response above   Dated 04/12/2004 06:19AM PDT clear enough?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10806120
Hi mate,

still pondering...

Active set is all records where the [Occurrence Dropped] = false    '<--  got this bit ok

Within the active set if there are no records marked [Occurrence Reference] and there has been a 4 month span
   of no records from the current date then
  '------todays date----------^----------- ??
     the oldest record within the active set needs to be marked  
     [Occurrence Dropped] and [Occurrence Reference]

So this returns current set:

SELECT OccurrenceID, Employee, [Date], [Occurrence Dropped], [Occurrence Reference]
FROM tblOccurrence
WHERE Employee="Thayer, Trygve" AND [Occurrence Dropped]=False

OccurrenceID      Employee      Date      Occurrence Dropped      Occurrence Reference
37948976      Thayer, Trygve      01-May-2003      No      No      
37948977      Thayer, Trygve      01-Jul-2003      No      No
37948978      Thayer, Trygve      01-Nov-2003      No      No


This bit confuses me: 'and there has been a 4 month span
   of no records from the current date'

I think this bit dumps us into the ELSE part because 01-Jul-2003 to 01-Nov-2003 is exactly 4 months but it is not greater than 4 months hmmm...

so Into the ELSE we go:
   'Within the active set if there is a record marked [Occurrence Reference] "   ' There isn't

END

Alan






0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10806619
tthayer,
I can't visualize why you are getting a type mismatch on opening tblOccurrence. I need to see the table you are
using.

Alan,
<I think this bit dumps us into the ELSE part because 01-Jul-2003 to 01-Nov-2003 is exactly 4 months but it is not greater than 4 months hmmm...>

You need another field to put the date the record set was marked to make comparison if  today's date
is 4 months away from the last date the recordset was marked.

01-Jul-2003  If this was marked 01-Dec-2003

01-Nov-2003 -you can not marked this on date 01-Jan-2004 because the difference  on the date the
                     recordset was marked {01-Dec-2003 and 01-Jan-2004} is less than 4 months.

Is this right tthayer?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10806726
Hi Rey,

do you have a reference to Activex Data Objects 2.xx and DAO 3.xx ??  

Alan
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10806903
Hi Alan,
Just DAO 3.6

Rey:-)
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10806906
catch ya later guys 2:50 am here, promised myself I wouldn't do a late one again.

Rey, if you have ref to both DAO and ADO must explicity define the the object vars or wont compile.
Dim db As DAO.Database, rst As DAO.Recordset

Ty,
Dont beat yaself up over this one it's a toughy.

lLater :)

Alan
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10806931
And even if i select both on the reference there is no problem.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10807737
tthayer,
Place the Microsoft DAO 3.6 Object Library on the third line by selecting it and clicking
the Up arrow priority.

Visual Basic for Applications
Microsoft Access 10 Object Library
Microsoft DAO 3.6 Object Library
0
 
LVL 3

Author Comment

by:tthayer
ID: 10808431
To Capricorn......

I have opened visual basic on the form frmOccurrence which uses a query called qryOccurrence and in there clicked tools and references.  In there the following items are checked.  Visual Basic for Applications, Microsoft Access 10.0 Object Library, OLE Automation, Microsoft Activex Data Access data objects 2.1 library, and Microsoft DAO 3.6 Object Library which is the one you had me add.  Is there something else I need to add to run your code?  I ran your code again and got the same type mismatch error.  I know this is a lack of understanding but I am not sure how to place the Microsoft DAO Object Library and select the up arrow.

To allenwarren.......maybe with the data and telling you what needs to happen will help with the confusion.
I am altering the data to make it hopefully easier.  

If I run the code on the current data below and the current date is 4/12/2004 there should not be any records marked [Occurrence Dropped] as there are not any over 1 year old.  The record on 5/1/2003 should be marked as [Occurrence Dropped] and [Occurrence Reference] as it has been 4 months since the last record of 11/1/2003 and 5/1/2003 is the oldest record in the active set.

If I run the code again then the next active record should not be marked as [Occurrence Dropped] and [Occurrence Reference] until 8/12/2004.  
Keep in mind if on 8/10/2004 a record is recorded then it can not be dropped because there has to be 4 months of no records




Data

 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 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      9/1/2003      1      Over 2             FALSE      FALSE
23      Thayer, Trygve      11/1/2003      2      Call In            FALSE      FALSE





0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10808717
<I know this is a lack of understanding but I am not sure how to place the Microsoft DAO Object Library and select the up arrow.>

From VBA window
Tools>References
on the References window
Select or highlight Microsoft DAO 3.6 Object Library

To the right of the Available references listbox you will see two Arrows  Up and Down with a label Priority.

Click the UP arrow until Microsoft DAO 3.6 is the third from the top of the list.

so you will have something like this

Visual Basic for Applications
Microsoft Access 10 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Activex Data Access data objects 2.1 library
OLE Automation

Click OK

On the Menu of the VBA window
Click on Debug>Compile

0
 
LVL 3

Author Comment

by:tthayer
ID: 10809939
allenwarren....I assume you are just waking up but like how your code does everything in one

Capricorn.....

I did what you said and got it to run. I went back to the previous Q to get the code and realized my problem was I over looked your instructions to add a field called MarkedDate.  I did the Odate but did not see the other.

Now that it runs I still have a field called [Occurrence Reference]  Do I need this field with your code?

When I ran your code I realized you are depending on another query to check and mark the records older than 1 year.  I interpret this one takes care of the other.  When I ran the code I did see the next record marked in [Occurrence Dropped] and every record after including the one marked had today's date in it of 4/12/2004 which is when I ran the code.  I don't quite understand how this works.  Could you please explain for me.  I want to make sure your code does the following but am not educated enough to know how to read the code.

Went and talked to our HR manager and he put it in a different perspective.  So lets see if this makes sence.  Records are either active  or inactive.  In the tblOccurrence Active on inactive is represented by the [Occurrence Dropped] field.  This one works correctly as records are marked (meaning inactive) if they are 1 year old.

The second part which is very close is where I feel I am having trouble getting the logic right.  As you have seen the documentation is quite complex.

There is a decision process here.

If
   Within the active set if there are no records marked [Occurrence Reference] and there has been a 4 month span
   of no records from the current date then the oldest record within the active set needs to be marked
   [Occurrence Dropped] and [Occurrence Reference]

ELSE
   Within the active set if there is a record marked [Occurrence Reference] and there has been a 4 month span of
   no records from the current date then the oldest record within the active set can only be marked if it is 4 months
   from the last [Occurrence Reference Field]
END
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10809952
<Now that it runs I still have a field called [Occurrence Reference]  Do I need this field with your code?>

You can keep it. my codes will run even if it is on the table.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10809954
mmmm coffee yum... morning.
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810025
capricorn.......

I tested your code further and based on the data below I did not expect it to mark 1/1/2003. I set my computer to be 5/1/2003.  Yes 1/1/2003 is less than 1 year back and is also 4 months from the current date but there has not been a 4 month span without records to allow the 1/1/2003 record to be dropped.

ID      Employee                      ODate      Value      Type    Comments   Dropped  Reference      MarkedDate
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      Contin      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       rrrr      TRUE      FALSE      5/1/2003
15      Thayer, Trygve      2/1/2003      1      Call In            FALSE      FALSE      5/1/2003
16      Thayer, Trygve      3/1/2003      1      Call In            FALSE      FALSE      5/1/2003
17      Thayer, Trygve      4/1/2003      1      Over 2            FALSE      FALSE      5/1/2003
1      Thayer, Trygve      4/10/2003      2      Call In  yep he did it.      FALSE      FALSE      5/1/2003
25      Thayer, Trygve      4/11/2003      1      Call In            FALSE      FALSE      5/1/2003
18      Thayer, Trygve      5/1/2003      2      Over 2             FALSE      FALSE      5/1/2003
19      Thayer, Trygve      9/1/2003      1      Over 2             FALSE      FALSE      5/1/2003
23      Thayer, Trygve      11/1/2003      2      Call In            FALSE      FALSE      5/1/2003
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810027
allenwarren.......G'Morning.  It is 10:10pm here in the States (Tenessee to be Exact)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810097
Hi Ty,

Have you changed the field names to:
ID     Employee    Date     Value     Type    Comments   Dropped   Reference

Is ok, just need know.

My Data now looks like this:
ID      Employee      Date      Value      Type      Comments      Dropped      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      01-May-2003      2      Over 2      target this      No      No
19      Thayer, Trygve      01-Sep-2003      1      Over 2            No      No
23      Thayer, Trygve      01-Nov-2003      2      Call In            No      No
25      Thayer, Trygve      11-Apr-2003      1      Call In            Yes      No


The field with the comments 'target this' is the one we want I believe.

If we are all synced up I will get into it.

Alan
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810123
alanwarren......I have created 2 databases.  The one we were working on is exactly as we left it.

---------------------------------------------------------Your first response this morning (your night)
Hi mate,

still pondering...

Active set is all records where the [Occurrence Dropped] = false    '<--  got this bit ok

Within the active set if there are no records marked [Occurrence Reference] and there has been a 4 month span
   of no records from the current date then
  '------todays date----------^----------- ??
     the oldest record within the active set needs to be marked  
     [Occurrence Dropped] and [Occurrence Reference]

So this returns current set:

SELECT OccurrenceID, Employee, [Date], [Occurrence Dropped], [Occurrence Reference]
FROM tblOccurrence
WHERE Employee="Thayer, Trygve" AND [Occurrence Dropped]=False

OccurrenceID     Employee     Date     Occurrence Dropped     Occurrence Reference
37948976     Thayer, Trygve     01-May-2003     No     No      
37948977     Thayer, Trygve     01-Jul-2003     No     No
37948978     Thayer, Trygve     01-Nov-2003     No     No


This bit confuses me: 'and there has been a 4 month span
   of no records from the current date'

I think this bit dumps us into the ELSE part because 01-Jul-2003 to 01-Nov-2003 is exactly 4 months but it is not greater than 4 months hmmm...

so Into the ELSE we go:
   'Within the active set if there is a record marked [Occurrence Reference] "   ' There isn't

END

Alan

-------------------------------------------My reply to your response this morning (your night)

To allenwarren.......maybe with the data and telling you what needs to happen will help with the confusion.
I am altering the data to make it hopefully easier.  

If I run the code on the current data below and the current date is 4/12/2004 there should not be any records marked [Occurrence Dropped] as there are not any over 1 year old.  The record on 5/1/2003 should be marked as [Occurrence Dropped] and [Occurrence Reference] as it has been 4 months since the last record of 11/1/2003 and 5/1/2003 is the oldest record in the active set.

If I run the code again then the next active record should not be marked as [Occurrence Dropped] and [Occurrence Reference] until 8/12/2004.  
Keep in mind if on 8/10/2004 a record is recorded then it can not be dropped because there has to be 4 months of no records




Data

 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 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     9/1/2003     1     Over 2           FALSE     FALSE
23     Thayer, Trygve     11/1/2003     2     Call In          FALSE     FALSE

0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810236
Hi Ty,

got it I think, just testing....

suppose there was more than one record in the active set that had a datediff > 4mths from the newest record in the active set, should I mark both as as [Occurrence Dropped] and [Occurrence Reference] ???

Alan

0
 
LVL 3

Author Comment

by:tthayer
ID: 10810294
If there are 4 months between the [Occurrence Reference] fields then yes drop both.

If there are not 4 months then only 1 gets dropped.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810339
Hi Ty,

In the current dataset I dont have any Occurence reference fields maked?
Then when I run my sub  ID 18 gets marked both reference and dropped = true
ID 18 is then the only record with a reference = true in the current data set

You didn't reply to my question about the field names now being:
ID     Employee                       Date     Value     Type    Comments   Dropped   Reference

I have changed mine in the table design accordingly, if you have not changed the table field names let me know and I will change mine back to original.

This is where I am up to, just need to add loop to affect this part.
   'If there are 4 months between the [Occurrence Reference] fields then yes drop both.'


Current code:

Private Sub Command11_Click()
  On Error GoTo ReportError
 
  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
 
  Dim dNewestDate As Date
  Dim dOldestDate As Date
 
  Dim lActiveOldest As Long
  Dim lActiveNewest 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.Dropped = True"
  strSQL = strSQL & " WHERE [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 defined as (active set)
  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
      ' open the active set for this employee
      strSQL = "SELECT ID, Employee, [Date], Dropped, Reference FROM tblOccurrence"
      strSQL = strSQL & " WHERE Employee='" & .Fields("Employee")
      strSQL = strSQL & "' AND Dropped=False"
      strSQL = strSQL & " Order By Date Desc"
      recOccurrence.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
      With recOccurrence
        If Not .BOF And Not .EOF Then
          .MoveFirst
          lActiveNewest = .Fields("ID")
          dNewestDate = Format(.Fields("Date"), "dd-mmm-yyyy")
          .MoveLast
          lActiveOldest = .Fields("ID")
          dOldestDate = Format(.Fields("Date"), "dd-mmm-yyyy")
          ' test the oldest against the newest for greater than 4 month diff
          If dOldestDate < DateAdd("m", -4, dNewestDate) Then ' off the hook
            Debug.Print dOldestDate & " is less than " & DateAdd("m", -4, dNewestDate)
            .Fields("Reference").Value = True
            .Fields("Dropped").Value = True
            .Update
          End If
        End If
        .Close
      End With
      .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 Form1_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


Current values in table:

ID      Employee      Date      Value      Type      Comments      Dropped      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      01-May-2003      2      Over 2      target this      Yes      Yes
19      Thayer, Trygve      01-Sep-2003      1      Over 2            No      No
23      Thayer, Trygve      01-Nov-2003      2      Call In            No      No
25      Thayer, Trygve      11-Apr-2003      1      Call In            Yes      No

Alan




0
 
LVL 3

Author Comment

by:tthayer
ID: 10810342
Allenwarren.....

to elaborate

If the current date is 4/12/2004 and the code has not been ran since 4/11/2003 then 4/15/2003 will be Dropped and Referenced because there is a 4 month span from 8/11/2003 to 12/11/2003 and there are no references in the active set.  5/15/2003 would also be dropped and referenced as there is another 4 month span from 12/11/2003 to 4/12/2003.  Maybe Capricorn had something there in marking the reference not on the record to be dropped but on the record the 4 month span was counted from?

4/15/2003
5/15/2003
8/11/2003
12/11/2003
4/12/2004
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810350
The fields are EmployeeID, Employee, Date, Occurrence Value, Occurrence Type, Occurrence Comments, Occurrence Dropped, and Occurrence Reference.
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810368
allenwarren.....

Here is the current data I have in the database

OccurrenceID      Employee      Date      Occurrence Value      Occurrence Type      Occurrence Comments      Occurrence Dropped      Occurrence Reference
10      Thayer, Trygve      1/2/1998      1      Call In            FALSE      FALSE
11      Thayer, Trygve      1/2/1999      1      Call In            FALSE      FALSE
12      Thayer, Trygve      1/2/2000      2      Call In            FALSE      FALSE
6      Thayer, Trygve      1/1/2001      2      Call In      tttt      FALSE      FALSE
7      Thayer, Trygve      1/1/2001      2      Call In      eeee      FALSE      FALSE
13      Thayer, Trygve      1/2/2001       1      Call In            FALSE      FALSE
8      Thayer, Trygve      1/1/2002       1      Call In      wwww      FALSE      FALSE
14      Thayer, Trygve      1/2/2002       1      Call In            FALSE      FALSE
5      Thayer, Trygve      3/30/2002       2      Call In            FALSE      FALSE
9      Thayer, Trygve      1/1/2003       1      Call In      rrrr      FALSE      FALSE
15      Thayer, Trygve      2/1/2003       1      Call In            FALSE      FALSE
16      Thayer, Trygve      3/1/2003       1      Call In            FALSE      FALSE
17      Thayer, Trygve      4/1/2003       1      Call In            FALSE      FALSE
1      Thayer, Trygve      4/10/2003       2      Call In      did it.      FALSE      FALSE
25      Thayer, Trygve      4/15/2003       1      Call In            FALSE      FALSE
18      Thayer, Trygve      5/15/2003       2      Call In            FALSE      FALSE
19      Thayer, Trygve      8/11/2003       1      Call In            FALSE      FALSE
23      Thayer, Trygve      12/11/2003 2      Call In            FALSE      FALSE
27      Thayer, Trygve      4/12/2004       1      Call In            FALSE      FALSE
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810370
Ok will rename the fields no problem, the code I just posted has wrong field names.

re: 'then 4/15/2003 will be Dropped and Referenced '

I dont got dis record in my table :(  



Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810378
EmployeeID ?? is this a typo? should be OccurrenceID yes?
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810389
Yes is is a typo.  It should be OccurrenceID.  Think I better get some coffee.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810393
syncing data...
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810452
Ok just ran on new data.

marked OccurenceID 25    true    true


did not mark: 18, 19, 23
because they are outside the active set

I have active set defined as:     (run this sql)
SELECT OccurrenceID, Employee, [Date], [Occurrence Dropped], [Occurrence Reference] FROM tblOccurrence WHERE Employee='Thayer, Trygve' AND [Occurrence Dropped]=False Order By Date Desc

Is my active set definition correct?

Alan
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810504
25 and 18 should be marked.  25 is marked as there is a 4 month span between 8/11/2003 and 12/11/2003.  18 should be marked because remembering 12/11/2003 there has been an additional 4 months from 12/11/2003 to 4/12/2004.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 10810531
Think this is good:

Private Sub Command11_Click()
  On Error GoTo ReportError
 
  Dim strSQL As String
  Dim strWhere As String
  Dim recOccurrence As ADODB.Recordset
  Dim recEmployee As ADODB.Recordset
 
  Dim dNewestDate As Date
  Dim dOldestDate As Date
 
  Dim lActiveOldest As Long
  Dim lActiveNewest 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. -It should then check all records BY EMPLOYEE
  '   from the current date back 4 months defined as (active set)
  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
      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"
      strSQL = strSQL & " Order By Date Desc"
      recOccurrence.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
      With recOccurrence
        If Not .BOF And Not .EOF Then
          .MoveFirst
          lActiveNewest = .Fields("OccurrenceID")
          dNewestDate = Format(.Fields("Date"), "dd-mmm-yyyy")
          .MoveLast
          lActiveOldest = .Fields("OccurrenceID")
          dOldestDate = Format(.Fields("Date"), "dd-mmm-yyyy")
          ' test the oldest against the newest for greater than 4 month diff
          If dOldestDate < DateAdd("m", -4, dNewestDate) Then ' off the hook
            Debug.Print dOldestDate & " is less than " & DateAdd("m", -4, dNewestDate)
            .Fields("Occurrence Reference").Value = True
            .Fields("Occurrence Dropped").Value = True
            .Update
          Else
            ' this gets us out of the inner loop
            Exit Do
          End If
        End If
        .Close
      End With

    Loop
    .MoveNext
    Loop
  End With
 
ExitProcedure:
  On Error Resume Next
  Set recOccurrence = Nothing
  Set recEmployee = Nothing
  Exit Sub

ReportError:
  Dim msg As String
  ' Modify the following to return correct Form Name and Procedure Name ********
  msg = "Error in Form1_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


Alan
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810539
allenwarren......

I  have learned a lot in how to express what I am needing to do.  I hope I am not frustrating you.  I will stick with the current data you are working from and stop changing it around.  In typing all this out I have realized that maybe I do not need to mark the [Occurrence Reference] field for a record to be dropped but in fact need to mark the [Occurrence Reference] field of the record from where I found the 4 month span.  That way I can use it to determine when the next 4 month span for a record to be dropped.  Quite simply they can only have 3 records dropped in the active year as a result of a 4 month span of no records.
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810548
Just saw your post after my last.  I will put it in.
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810574
alanwarren.....Here is what it returned   25,18,19, and 23 returned True True.   19 and 23 should still be False False

OccurrenceID      Employee      Date      Occurrence Value      Occurrence Type      Occurrence Comments      Occurrence Dropped      Occurrence 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      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     did it.      TRUE      FALSE
25      Thayer, Trygve      4/15/2003      1      Call In            TRUE      TRUE
18      Thayer, Trygve      5/15/2003      2      Call In            TRUE      TRUE
19      Thayer, Trygve      8/11/2003      1      Call In            TRUE      TRUE
23      Thayer, Trygve      12/11/20032      Call In            TRUE      TRUE
27      Thayer, Trygve      4/12/2004      1      Call In            FALSE      FALSE
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810628
alanwarren.......

I am again rewarding you with the points because you deserve it.  I appreciate your patience in helping me with this issue.  You have spent many hours in helping me.  please see the next post. I hope you are willing to continue.

http://www.experts-exchange.com/Databases/MS_Access/Q_20951874.html
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810630
oh :(

I'm comparing each date to Last Date in active set(4/12/2004), what you want is to compare to next date in active set.

got the picture... :)

fixing...

Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810674
I'll sort it out mate, get some sleep... currently 1:00 am there hey.

Alan
0
 
LVL 3

Author Comment

by:tthayer
ID: 10810763

Check the new post.  http://www.experts-exchange.com/Databases/MS_Access/Q_20951874.html

I think I came up with a better way to explain the logic or I at least tried.  I look forward to seeing your comments in the morning (your night)  I will leave the data the same.  If you come up with some ideas that require changing some fields or adding I will be glad to to do.
0
 
LVL 3

Author Comment

by:tthayer
ID: 10813793
I have received no replies on my next post so I deleted it and added it back.  It did no appear to have posted correctly.  Here is the new link

http://www.experts-exchange.com/Databases/MS_Access/Q_20952366.html
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10817145
Hi Ty,
I have not got a solution yet mate.
I will post what I have at the new thread shortly.

Alan
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now