Add years to the latest date

[smally]

I have a table that stores medicals for employees.

Employee's must attend a health assessment medical every 'X' number of years. In this table the fields are:
EmployeeID (a foreign key from tblEmployees)
MedDate (the date of the medical)
Result (keywords to determine the overall outcome of the assessment e.g. ok, sub-optimal, unfit)
Observations (memo with details of the medical report)

Since employees must have a medical every 'X' number of years, I'd like my database to tell me when a medical will expire. The 'X' is dependant on the employee's age, and result from the medical assessment.

My query is currently:

SELECT tblMedicalRecords.EmployeeID, Max(GetMedicalExpiry([MedDate],[DOB],[Result])) AS Med_Exp
FROM tblEmployees INNER JOIN tblMedicalRecords ON tblEmployees.EmployeeID = tblMedicalRecords.EmployeeID
GROUP BY tblMedicalRecords.EmployeeID;

And GetMedicalExpiry is a function:

Function GetMedicalExpiry(medDate As Date, DOB As Date, strResult As Variant) As Date

    Dim iAge As Integer
    Dim i As Integer

    iAge = age(DOB) ' Function that returns the age from a date of birth

    Select Case iAge
        Case 0 To 54    ' Ages 0 to 54 have medicals every 3 years
            i = 3
        Case 55 To 64   ' Ages 55 to 64 have medicals every 2 years
            i = 2
        Case 65 To 1000 ' Ages 65+ have medicals yearly
            i = 1
    End Select

    Select Case strResult
        Case Is = "Sub-Optimal" ' If medical is reported as sub-optimal, another
            i = 1               ' medical must be done it 1 year
        Case Is = "Unfit"   ' If medical is reported as unfit, don't add any
            i = 0           ' years
    End Select
    
    i = i * 12

    GetMedicalExpiry = DateAdd("m", i, medDate)
End Function

The problem I've discovered is my query actually shows the latest expiry date when I actually want the latest MedDate + years.

For example if a 30 year old had a medical 30/01/2015 that was Satisfactory, and another medical one month later 25/02/2015 that was Sub-Optimal, then my current query would display an expiry date of 30/01/2018, when I want is to show 25/02/2016 because the February one was that employee's latest medical.

 

[sneuberg]

It appears you need to get the latest MedDate before you applied the GetMedicalExpiry function. So get the lastest MedDate which I name Lastest_Med_Date in this query:

SELECT tblMedicalRecords.EmployeeID, Max([MedDate]) AS Lastest_Med_Date
FROM tblEmployees INNER JOIN tblMedicalRecords ON tblEmployees.EmployeeID = tblMedicalRecords.EmployeeID
GROUP BY tblMedicalRecords.EmployeeID;

Then you could join this query back on the other tables on EmployeeID and Lastest_Med_Date to get the result associate with that EmployID and MedDate then apply the function with this data.

 

[arnelgp]

you need to add a condition on your function:

Function GetMedicalExpiry(medDate As Date, DOB As Date, strResult As Variant) As Date

    Dim iAge As Integer
    Dim i As Integer

    iAge = age(DOB) ' Function that returns the age from a date of birth

    If InStr("Sub-Optimal/Unfit", strResult & "") > 0 Then
        Select Case strResult
            Case Is = "Sub-Optimal" ' If medical is reported as sub-optimal, another
                i = 1               ' medical must be done it 1 year
            Case Is = "Unfit"   ' If medical is reported as unfit, don't add any
                i = 0           ' years
        End Select
    Else
        Select Case iAge
            Case 0 To 54    ' Ages 0 to 54 have medicals every 3 years
                i = 3
            Case 55 To 64   ' Ages 55 to 64 have medicals every 2 years
                i = 2
            Case 65 To 1000 ' Ages 65+ have medicals yearly
                i = 1
        End Select
    End If
    
    i = i * 12

    GetMedicalExpiry = DateAdd("m", i, medDate)
End Function

 

[smally]

So I've had a go with your method sneuberg. And I've used http://www.techonthenet.com/sql/max.php first FAQ as an example (I think it expands on what you suggested).

I now have

SELECT mr.EmployeeID, mr.MedDate, mr.ProvidedBy, mr.Result, GetMedicalExpiry([MedDate],[DOB],Nz([Result])) AS Med_Exp
FROM (SELECT EmployeeID, MAX(MedDate) AS Latest_MedDate FROM tblMedicalRecords GROUP BY EmployeeID)  AS maxresults, tblEmployees INNER JOIN tblMedicalRecords AS mr ON tblEmployees.EmployeeID = mr.EmployeeID
WHERE (((mr.EmployeeID)=[maxresults].[EmployeeID]) AND ((mr.MedDate)=[maxresults].[Latest_MedDate]))
ORDER BY mr.EmployeeID;

I've only ever done SQL in Access' query designer so I'm not 100% this is correct or the best method. I think it works

 

[smally]

I've got a new error.

If I try to include the new Med_Exp in a query that displays all employees with a LEFT JOIN, it shows an #Error for those without a medical. How do I change this to show blank

I've attached an example database showing my problem.

 

[sneuberg]

After hours of putzing this really has me stumped. I got do other stuff now so I'll leave you with a function that seems to work that based on the employee id so you can plug in anywhere you have that. If you don't have a lot of records this might be a solution for you.

Function GetMedicalExpiry(EmployeeID As Long) As Variant

    Dim iAge As Integer
    Dim i As Integer
    Dim DOB As Date
    Dim LastestMedDate As Date
    Dim strResult As String

    DOB = Nz(DLookup("[DOB]", "[tblEmployees]", "[EmployeeID] = " & EmployeeID))
    LastestMedDate = Nz(DMax("[MedDate]", "[tblMedicalRecords]", "[EmployeeID] = " & EmployeeID))
    If LastestMedDate = 0 Then
        GetMedicalExpiry = Null
        Exit Function
    End If
    
    strResult = Nz(DLookup("[Result]", "[tblMedicalRecords]", "[MedDate] = #" & LastestMedDate & "# AND [EmployeeID] = " & EmployeeID))
    
    
    
    iAge = age(DOB) ' Function that returns the age from a date of birth

    Select Case iAge
        Case 0 To 54    ' Ages 0 to 54 have medicals every 3 years
            i = 3
        Case 55 To 64   ' Ages 55 to 64 have medicals every 2 years
            i = 2
        Case 65 To 1000 ' Ages 65+ have medicals yearly
            i = 1
    End Select

    Select Case strResult
        Case Is = "Sub-Optimal" ' If medical is reported as sub-optimal, another
            i = 1               ' medical must be done it 1 year
        Case Is = "Unfit"   ' If medical is reported as unfit, don't add any
            i = 0           ' years
    End Select

    i = i * 12

    GetMedicalExpiry = DateAdd("m", i, LastestMedDate)
    
End Function

 

[smally]

This one confused me for a good few hours too.

My workaround was to change GetMedicalExiry to return the number of months instead of expiry date.
Then in my query that needs to show all employees, and what should show null for those without a medical, I use the DateAdd function and use the months returned from the function.
Now if a record in the medical field is null, adding a date to it still returns null.

I think this issue is a glitch in Access as mentioned: http://allenbrowne.com/BugOuterJoinExpression.html

 

[sneuberg]

Thanks for the info about this being a bug.