Hi. I'd like a bit of advice for how I need to design my database to log medical information for my employees and calculate their due date(s) for another medical.
Each of my employees are required to attend a medical assessment for them to work on a construction site.
The medical simply covers hearing, lung function, skin assessment etc. Medicals are performed by an external company, who provide me with an overall report similar to a traffic light system.
The employee's medical is either marked as satisfactory (green), sub-optimal (amber), or unfit (red).
Each employee will need another medical every X years (X depends on their age).
However if their report states something is sub-optimal, annual reviews are required until their report goes to satisfactory.
Examples:
John Smith has a medical 2/3/2017. His medical was satisfactory, he will need another medical due for 2/3/2020.
David Jones also had a medical 2/3/2017. His hearing was sub-optimal, he will require a medical review for 2/3/2018.
His medical review on 2/3/2018 states his hearing is still sub-optimal, he will require another medical review for 2/3/2019.
His medical review on 2/3/2019 states his hearing is still sub-optimal, he will require another annual review however... his full medical will be due:
He will need another full medical due for 2/3/2020.
Andy Johnson also had a medical 2/3/2017. His hearing was sub-optimal, he will require a medical review for 2/3/2018.
His medical review on 2/3/2018 states his hearing is now satisfactory, he will not require a medical review.
He will need another full medical due for 2/3/2020.
I've been using this vba code to calculate due dates in queries, however this does not work properly when it takes the medical reviews.
Each of my employees are required to attend a medical assessment for them to work on a construction site.
The medical simply covers hearing, lung function, skin assessment etc. Medicals are performed by an external company, who provide me with an overall report similar to a traffic light system.
The employee's medical is either marked as satisfactory (green), sub-optimal (amber), or unfit (red).
Each employee will need another medical every X years (X depends on their age).
However if their report states something is sub-optimal, annual reviews are required until their report goes to satisfactory.
Examples:
John Smith has a medical 2/3/2017. His medical was satisfactory, he will need another medical due for 2/3/2020.
David Jones also had a medical 2/3/2017. His hearing was sub-optimal, he will require a medical review for 2/3/2018.
His medical review on 2/3/2018 states his hearing is still sub-optimal, he will require another medical review for 2/3/2019.
His medical review on 2/3/2019 states his hearing is still sub-optimal, he will require another annual review however... his full medical will be due:
He will need another full medical due for 2/3/2020.
Andy Johnson also had a medical 2/3/2017. His hearing was sub-optimal, he will require a medical review for 2/3/2018.
His medical review on 2/3/2018 states his hearing is now satisfactory, he will not require a medical review.
He will need another full medical due for 2/3/2020.
I've been using this vba code to calculate due dates in queries, however this does not work properly when it takes the medical reviews.
Code:
Function GetMedicalExpiry(medDate As Date, DOB As Date, strResult As String) 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 review 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