How can I reference a subquery from within a query I have one query:
Code:
SELECT tblDelegate.ID, tblDelegate.DelegateFname, tblDelegate.DelegateLname, tblDelegate.DelegateMobileNumber, tblDelegate.DelegateEmailAddress, tblEventDelegate.Status, tblDelegate.SubTeamID, tblSubTeam.SubTeamHeadDelID, tblSubTeam.SubTeamParentID
FROM tblSubTeam INNER JOIN (tblDelegate INNER JOIN tblEventDelegate ON tblDelegate.ID = tblEventDelegate.DelegateID) ON tblSubTeam.ID = tblDelegate.SubTeamID
WHERE (((tblEventDelegate.Status)=1) AND ((tblSubTeam.SubTeamParentID)=IIf([tblSubTeam].[SubTeamHeadDelID]=[tblDelegate].[ID],[SubTeamParentID],[SubTeamHeadDelID])));
this shows the delegates who are listed as SubteamHeadDelIDs and works but I need to somehow incorporate that into another query which will show all the delegates and show those that are SubTeamHeadDels by showing their SubTeamParentID.