Calculating consecutive years in FT or PT status

[MADDPM]

I need to calculate consecutive years of service in part-time or full-time status. If this has been answered in another thread, please refer me to it – I’ve searched for the better part of a day and didn’t find anything relevant.

For my scenario - a temporary employee may work:
2009-10 – Full time
2008-09 – Full time
2007-08 – Full time
2006-07 – Part time
2005-06 – Part time
2004-05 – Full time

I need to calculate number of academic years by “groups” in status – in other words, not cumulative. For example using the above information:
2009-10 – Full time (count = 3 consecutive years)
2008-09
2007-08
2006-07 – Part time (count = 2 consecutive years)
2005-06
2004-05 – Full time (count = 1 consecutive years)

I have the following structure:
TABLE: EMPLOYEE
Relevant FIELD: [EMPLOYEE_Number]

TABLE: APPOINTMENT
Relevant FIELDs:
[Employee_Number]
[APPOINTMENT_ID]
[Begin_Date]
[Expire_Date]
[FT_PT]

TABLE: CONTRACT
Relevant FIELDs:
[Appointment_ID]
[Contract_AY] (This is a text field)

Can anyone help?

(using: Access 2007, Windows XP)

Thanks in advance-
Colleen

 

[jesmiles]

Hi Colleen,

I'm still a little confused about your tables you listed above, but it looks like the "Appointment" table is the most relevant, since it lists the Begin Date and employment status. Not sure how you need to be able to show this, but I would suggest creating a new table with:

[Employee_Number]
[Date_Range]
[FT_PT]
[Count]

and insert your new values into this table.

I created a module, which should work if you create the table I mentioned above. Yes, I still use DAO which is kind of antiquated, but the code should work fine

Option Compare Database
Option Explicit

Private Sub CalculateService()

Dim rsA As DAO.Recordset
Dim rsS As DAO.Recordset
Dim lngEmp As Long
Dim strBeg As String
Dim strType As String
Dim intCount As Integer

Set rsA = CurrentDb.OpenRecordset("SELECT * FROM [Appointments] ORDER BY [Employee_Number],[Begin_Date]")
Set rsS = CurrentDb.OpenRecordset("SELECT * FROM [Service]")

rsA.MoveFirst
lngEmp = rsA!Employee_Number
strBeg = rsA!Begin_Date
strType = rsA!FT_PT
intCount = 0

Do
'If we are continuing with the same employee
If lngEmp = rsA!Employee_Number Then

'If we are continuing with the same employee and service type, keep counting
If strType = rsA!FT_PT Then
intCount = intCount + 1
rsA.MoveNext

'If we are continuing with the same employee but different service type, add to new table
' and reset counters and string variables
Else:
rsA.MovePrevious
rsS.AddNew
rsS!Employee_Number = lngEmp
rsS!Date_Range = strBeg & " - " & rsA!Expire_Date
rsS!FT_PT = strType
rsS!Count = intCount
rsS.Update
rsA.MoveNext
If Not rsA.EOF Then
strBeg = rsA!Begin_Date
strType = rsA!FT_PT
intCount = 0
End If
End If

'If we have encountered a new employee add to new table
' and reset counters and string variables
Else:
rsA.MovePrevious
rsS.AddNew
rsS!Employee_Number = lngEmp
rsS!Date_Range = strBeg & " - " & rsA!Expire_Date
rsS!FT_PT = strType
rsS!Count = intCount
rsS.Update
rsA.MoveNext
If Not rsA.EOF Then
strBeg = rsA!Begin_Date
strType = rsA!FT_PT
intCount = 0
End If
lngEmp = rsA!Employee_Number
strBeg = rsA!Begin_Date
strType = rsA!FT_PT
intCount = 0
End If

If rsA.EOF Then
rsA.MovePrevious
rsS.AddNew
rsS!Employee_Number = lngEmp
rsS!Date_Range = strBeg & " - " & rsA!Expire_Date
rsS!FT_PT = strType
rsS!Count = intCount
rsS.Update
rsA.MoveNext
End If

Loop While Not rsA.EOF

Set rsA = Nothing
Set rsS = Nothing

MsgBox ("Process Complete!")

End Sub

 

[jesmiles]

I adjusted to show the code properly

Option Compare Database
Option Explicit

Private Sub CalculateService()

Dim rsA As DAO.Recordset
Dim rsS As DAO.Recordset
Dim lngEmp As Long
Dim strBeg As String
Dim strType As String
Dim intCount As Integer

Set rsA = CurrentDb.OpenRecordset("SELECT * FROM [Appointments] ORDER BY [Employee_Number],[Begin_Date]")
Set rsS = CurrentDb.OpenRecordset("SELECT * FROM [Service]")

rsA.MoveFirst
lngEmp = rsA!Employee_Number
strBeg = rsA!Begin_Date
strType = rsA!FT_PT
intCount = 0

Do
    'If we are continuing with the same employee
    If lngEmp = rsA!Employee_Number Then
    
        'If we are continuing with the same employee and service type, keep counting
        If strType = rsA!FT_PT Then
            intCount = intCount + 1
            rsA.MoveNext
            
        'If we are continuing with the same employee but different service type, add to new table
        '   and reset counters and string variables
        Else:
            rsA.MovePrevious
            rsS.AddNew
            rsS!Employee_Number = lngEmp
            rsS!Date_Range = strBeg & " - " & rsA!Expire_Date
            rsS!FT_PT = strType
            rsS!Count = intCount
            rsS.Update
            rsA.MoveNext
            If Not rsA.EOF Then
                strBeg = rsA!Begin_Date
                strType = rsA!FT_PT
                intCount = 0
            End If
        End If
        
    'If we have encountered a new employee add to new table
    '   and reset counters and string variables
    Else:
        rsA.MovePrevious
        rsS.AddNew
        rsS!Employee_Number = lngEmp
        rsS!Date_Range = strBeg & " - " & rsA!Expire_Date
        rsS!FT_PT = strType
        rsS!Count = intCount
        rsS.Update
        rsA.MoveNext
        If Not rsA.EOF Then
            strBeg = rsA!Begin_Date
            strType = rsA!FT_PT
            intCount = 0
        End If
        lngEmp = rsA!Employee_Number
        strBeg = rsA!Begin_Date
        strType = rsA!FT_PT
        intCount = 0
    End If
    
    If rsA.EOF Then
        rsA.MovePrevious
        rsS.AddNew
        rsS!Employee_Number = lngEmp
        rsS!Date_Range = strBeg & " - " & rsA!Expire_Date
        rsS!FT_PT = strType
        rsS!Count = intCount
        rsS.Update
        rsA.MoveNext
    End If

Loop While Not rsA.EOF

Set rsA = Nothing
Set rsS = Nothing

MsgBox ("Process Complete!")

End Sub

 

[MADDPM]

Hi jesmiles,

In theory, the module is exactly what I need. I created the new table and the module as you instructed - but I can't figure out how to call CalculateService() or get it to work.
I need to be able to show this in both, a form and a report.

Thanks so much for your help so far!
-Colleen

 

[MADDPM]

Hi again, jesmiles,
I tried calling the function from a query on a report:

"ConsecutiveYrs: CalculateService([Count])"

But I am receiving an error: "Wrong number of arguments used with function in query expression 'CalculateService([Count]!"

Any ideas?

thanks in advance-
colleen

 

[MADDPM]

Calling the function from the same query but different method:

Field: Count
Table: YEARSOFSERVICE
Sort: (empty)
Show: (checked)
Criteria: CalculateService()

results in an error: Data type conversion error
and when I click on debug, the highlighted line of code is in the second Else process:
rsS!Date_Range = strBeg & " - " & rsA!Expire_Date_this_appt

-c

 

[MADDPM]

Okay, I am a pest .... and module function runs (I set the data type for the new table/field range as date/time - instead it should have been text).

HOWEVER, it runs just fine, and I get the little message at the end, but alas no data in the query table.

Suggestions are welcome.
Thanks again-
Colleen