Nesting SELECT in query with IIf

Question

I'm tasked to build a query in MS Access.

IMPORTANT

The tables and data are all examples. I can't post actual data since it's PII. I did my best to create a mock-up of what my data shows. Assume that spelling is not the problem.

I have a table tblMemberInfo that includes peoples' information. I'm creating a report that will list each member by tblMemberInfo.MemberID followed by their DateCompleted for each EventID. This works currently as is. However we need to show if they are exempt based upon what job position they hold as referenced in tblMemberInfo.PositionID joined with tblPositionExemptions.PositionID.

tblMemberCompletionDates

 MemberID  | EventID | DateCompleted
-----------+---------+--------------
1234567890 | English |   1/1/2017
1234567890 |  Math   |   5/8/2017
2345678901 | English |   1/1/2017
2345678901 |  Math   |   9/5/2017

tblMemberInfo

 MemberID  | PositionID | more... (like First/Last Name)
-----------+------------+-------------------------------
1234567890 |      1     |   
1234567890 |      1     |    
2345678901 |      2     |    
2345678901 |      2     |    

tblPositionExemptions

 PositionID  | EventID
-------------+--------
      1      | English 
      1      | History
      1      | Science  
      2      |  Math

This is the anticipated output I'm looking for.

qryGetMemberCompletionDates

 MemberID  | EventID | DateCompleted
-----------+---------+--------------
1234567890 | English |   EXCLUDED
1234567890 |  Math   |   5/8/2017
2345678901 | English |   1/1/2017
2345678901 |  Math   |   EXCLUDED

This query will obviously return everything in tblMemberCompletionDates. However like I said I need to show "EXCLUDED".

SELECT 
    tMCD.MemberID, tMCD.EventID, tMCD.DateCompleted
FROM 
    tblMemberCompletionDates as tMCD

So I made this column in the query that works, however it is PAINFULLY slow since the query is 4836 rows alone. There are also about 200 members and each have about 45 possible. If I set the EventID column to only show those that equal English it takes about 10 seconds to complete to return the 200 rows for each member. Simple math makes it about 500 seconds to run the full query.

DateDue: IIf([qryGetMemberCompletionDates].[EventID]=
             DLookUp("[EventID]","tblPositionExemptions",
                     "PositionID= '" & DLookUp("[PositionID]",'tblMemberInfo', "MemberID= '" 
                                               & [qryGetMemberCompletionDates].[MemberID] & "'") & "'"),
             "EXCLUDED",[qryGetMemberCompletionDates].[DateCompleted])

Is there a way to make it read faster? I'm leaning to want to do something like this pseudo code.

SELECT 
    MemberID, EventID, 
    IIF([qryGetMemberCompletionDates].[EventId] = 
             (SELECT EventID FROM tblPositionExemptions 
              WHERE EventID = "'" & qryGetMemberCompletionDates].[EventId] & "'" 
                AND PositionID = (SELECT PositionID FROM tblMemberInfo 
                                  WHERE MemberID = "'" & [qryGetMemberCompletionDates].[EventId] & "'")),
              "EXCLUDED", [qryGetMemberCompletionDates].[DateCompleted]) AS DueDate

FROM qryGetMemberCompletionDates AS qGMCD
GROUP BY qGMCD.MemberID, qGMCD.EventID, 
         IIf([qryGetMemberCompletionDates].[EventId] = 
              (SELECT EventID FROM tblPositionExemptions 
               WHERE EventID = "'" & qryGetMemberCompletionDates].[EventId] & "'" 
              AND PositionID = (SELECT PositionID FROM tblMemberInfo 
                                 WHERE MemberID = "'" & [qryGetMemberCompletionDates].[EventId] & "'")),
              "EXCLUDED",[qryGetMemberCompletionDates].[DateCompleted])

Answer 1

Do the joins first:

Select 
qGMCD.*, tblMemberInfo.*, tblPositionExemptions.*  
from tblPostioinExemptions 
inner join (tblMemberInfo  
  inner join qryGetMemberCompletionDates as qGMCD on 
    qGMCD.EventID = tblMemberInfo.MemberID) on tblPostioinExemptions ...

If a combination of inner, left, right joins won't give you the correct records, do separate queries and join the results with a Union query.

Run your IIF and grouping on the result of the joined/unioned data. You want all your joins, and if possible all your groups, to use indexed data.

Or, if you are automating this and have more work to do, create a temporary grouped table and run your IIF on that.

Answer 2

Consider using a LEFT JOIN with a conditional aggregate for the DateCompleted field:

SELECT tMCD.MemberID, tMCD.EventID, 
       MAX(IIf(tMCD.EventID=tPE.EventID,'EXCLUDED',tMCD.DateCompleted)) AS DateCompleted
FROM (tblMemberCompletionDates AS tMCD 
LEFT JOIN tblMemberInfo AS tMI 
  ON tMCD.MemberID = tMI.MemberID) 
LEFT JOIN tblPositionExemptions AS tPE 
  ON tMI.PositionID = tPE.PositionID
GROUP BY tMCD.MemberID, tMCD.EventID;

To diagram, the blue represents the IIF() condition with usual black arrows as LEFT JOIN. So you are checking for both conditions at same time.