Hey,
so this is the SQL-code of the three queries in question
Code:
SELECT [tblEQ-5D].*, EQ5D([Mobility],[Autonomy],[Activity],[Pain],[Anxiety]) AS [Overall Score]FROM [tblEQ-5D];
All three of them do basically the same, just on data formatted differently. EQ-5D() is a function locally defined via a VBA Module, and works fine whenever there is data. I have also implented internal checking of the parameters provided for = 0 and IsNull(parameter):
Code:
Public Function EQ5D( _
Mobility As Integer, _
SelfCare As Integer, _
UsualActivities As Integer, _
PainDiscomfort As Integer, _
AnxietyDepression As Integer _
) As Variant
Dim Score As Single
Score = 1
If Mobility = 0 Or SelfCare = 0 Or UsualActivities = 0 Or PainDiscomfort = 0 Or AnxietyDepression = 0 Then Exit Function
If IsNull(Mobility) Or IsNull(SelfCare) Or IsNull(UsualActivities) Or IsNull(PainDiscomfort) Or IsNull(AnxietyDepression) Then Exit Function
'do stuff
EQ5D = Math.Round(Score, 3)
End Function
All tables, queries etc are executed locally within the Access file.
An #Error is thrown wherever there is a patient encounter (the primary table) with no matching entry for the questionnaires. However, with the SQL code posted previously, as every single encounter is listed, the questionnaire query will get called even if there is no questionnaire.
If it was only for one distinct type of questionnaire, I could simply use INNER JOIN to display only matching entries. However, I need to display every encounter with at least one type of questionnaire leading to the situation where I needed to use several LEFT JOIN clauses. This however resulted in #Errors where no matching questionnaire is found.
I hope this explains the issue better, I am glad to clarify where needed