Difficult function!(?)

[Les Isaacs]

Hi All

I have a table [staffs] with fields [staff_name] and [leaving_date], and another table [sickness absences] with fields [employeename], [absencedate] and [DD].

[staff_name] is related to [employeename] - with referential integrity enforced. For many records in [staffs] there are no related records in [sickness absences] (not all staff have been ill!). There is one record in [sickness absences] for each date on which the employee was ill.

[DD] is a true/false field in [sickness absences].

That's the data: here's the problem.

I need a function to return a date (StartDate) for each employee that is 365 days earlier than their [leaving_date] ... but ... the 365 days must exclude any days in [sickness absences] for that employee that are within the 365 days and where [DD] = true.

e.g. for employee Peter Green
[leaving_date] = 22/7/12, no related records in [sickness absences]
So StartDate = 23/7/11

for employee John Black
[leaving_date] = 22/7/12, the only related records in [sickness absences] where [DD] = true are <23/7/11
So again StartDate = 23/7/11

for employee Alan Red
[leaving_date] = 22/7/12, there are 5 related records in [sickness absences] between 22/7/12 and 23/7/11 where [DD] = true
So StartDate = 17/7/11

So far, easy enough: the interesting cases are where there are further [DD] = true dates within the ‘extended’ year. Ultimately there must be 365 days – none of which have [DD] = true – between the returned [StartDate] and the given [leaving_date]. So each 'extension' could include further dates where [DD] = true, so we have to extend back again, until we have 365 days where there are no [DD] = true records in [sickness absences].

I may have been a bit long winded in explaining this, but hopefully someone is still with it and able to help. I'm thinking some sort of recursive thing is required, but am a bit out of my depth

Many thanks for any help.
Les

 

[plog]

I think I got it. First create a query named 'IncludedAbsences' using this SQL:

SELECT [sickness absences].employeename, [sickness absences].absencedate
FROM [sickness absences]
WHERE ((([sickness absences].DD)=True));

That will hold all the valid absences for everyone. Next, paste this code into a module:

 Function getStartDate(emp, ldate) As Date
      ' takes employee and their leaving date and calculates their starting date (365 days prior to leaving, minus any absences)
     
daysprior = 365
    ' starts by looking at date 365 days prior to leaving date
absences = DCount("[employeename]", "IncludedAbsences", "[absencedate]>#" & DateAdd("d", -1 * daysprior, ldate) & "# AND [employeename]='" & emp & "'")
     ' gets absences in prior 365 days
     
daysprior = daysprior + absences
     ' adds absences to 365 to get how many days they actually need to go back
     
Do While (daysprior - absences) < 365
    ' this finds any absences that occured greater than 365 days ago, but before possible starting date
    daysprior = daysprior + 1
    absences = DCount("[employeename]", "IncludedAbsences", "[absencedate]>#" & DateAdd("d", -1 * daysprior, ldate) & "# AND [employeename]='" & emp & "'")
    Loop
    
getStartDate = DateAdd("d", -1 * daysprior, ldate)
End Function

Finally, this SQL will produce your starting dates:

SELECT staffs.staff_name, staffs.leaving_date, getStartDate([staff_name],[leaving_date]) AS starting_date
FROM staffs;

Essentially it starts looking back 365 days prior for everyone, and then moves back a day (366, 367) until the number of prior days minus the absences between the leaving date and the proposed starting date equals 365.

 

[Les Isaacs]

Plog
Perfect!!
I am so grateful: I think I had the right idea, but was a million miles from getting the syntax etc. Your method has the added advantage that I can now see how to set up the loop, so will use that elsewhere.
Thanks also for not telling me off about the field names, db design, ... which often happens to me.
Thanks again
Les