IIf help

[Opal]

I'm not sure how to approach this so would
appreciate some guidance. I'm thinking
I need an IIF statement in my query... but
would appreciate any other suggestions.

I am running Access 2003. I have a query
which shows when a supervisor has
completed giving a course to his team.
There are, however, times, when the training
is given but a team member is absent and
this comes up as a deficiency.

This is my SQL for the course completion:

SELECT Max(qryMSMax.MaxOfMSComplID) AS MaxOfMaxOfMSComplID,
qryMSMax.SupLink, qryMSMax.MSCourseID, MSCompl.CompletionDate
FROM MSCompl INNER JOIN qryMSMax ON MSCompl.MSComplID =
qryMSMax.MaxOfMSComplID
GROUP BY qryMSMax.SupLink, qryMSMax.MSCourseID, MSCompl.CompletionDate
HAVING (((qryMSMax.SupLink)=[Forms]![HoldingInfo]![txtHoldSupNumber]))
ORDER BY qryMSMax.SupLink, qryMSMax.MSCourseID;

I have another table with the following fields
that details the names of the team members who
are deficient in the training:

MSDefID
GLLink
Name
EmpNumber
MSCourseID
MSDeficiency

I was thinking of something along the
lines of:

Deficient: IIf([TSMSDeficient]=-1,"Yes","None")

In my test case the supervisor had administered
3 courses with one team member deficiency in
one course. Ideally, my query results should
read (transposed, of course)

MaxOfMaxOfMSComplID 95 99 102
SupLink AB12 AB12 AB12
MSCourseID 1 2 3
CompletionDate 08/17/09 08/21/09 08/03/09
Deficient No No Yes

Unfortunately, all I get is

MaxOfMaxOfMSComplID 102
SupLink AB12
MSCourseID 3
CompletionDate 08/03/09
Deficient Yes

Can someone help me create a query which
will show me my desired results?

 

[Opal]

I got it....Its those joins....

LEFT JOIN MSDeficient ON MSCompl.MSComplID = MSDeficient.MSComplID


Those joins always mess me up