Complex query to me anyway

[AndycompanyZ]

I have posted this before on another board but I'm stuck with even thinking about how to go about this. I need to create a query so I can build a datasheet off of it. The query will look at a number of tables and select all those delegates who are scheduled onto an event. I then need to do the following which I've written in VBA (and this is pseudocode of what i want to do) but need to translate this into a query somehow:
If the delegateID in a subteam is = to the SubTeamHeadDelID then


Show the subTeamParentID and details of this person (who has their details in the delegate details tabel (everyone is in this table)) but if there is no SubTeamParentID in the table then I don't need to do anything.
I envisage the datasheet as being a list of delegates with columns showing their details and the phonenumber of the person(SubTeamParent or SubTeamDelID) to contact. It seems to me insurmountable in a query. I have the following in VBA:

 
If Me!SubTeamHeadDelID = Me!DelegateID Then
Dim strID As String
Dim strID2 As String
Debug.Print SubTeam
strID = DLookup("SubTeamParentID", "tblSubTeam", "SubTeam = " & Me!SubTeam)

If strID = Null Then
MsgBox "You don't need to contact anyone or whatever is needed"
Else
strID2 = DLookup("DelegateMobileNumber", "tblDelegate", "ID =" & strID)
MsgBox "Please contact this SubTeamLeader & strID2", vbOKOnly
End If
End If

which I had hoped to use but the syntax is wrong anyway but I need the same result in a query. I've been reading as much as i can on queries but nothing seems what i want to do. I think it maybe a subquery or 2 one to show who is a delegate and a subteamhead and then one to show who is a SubTeamParent and then include that in my query somehow. I'm a bit confused to say the least.

[AndycompanyZ]

Have been working on this and now have a query that shows the SubTeamParentID if the SubTeamHeadDelID are the same I imagine I need to put this into another query and use this as a subquery in the sense of Show me the records of delegates scheduled on courses and show me the SubTeamParentIDs of those who are the same as the SubteamHeadDelID. But I don't know how to refer to a subquery within a query

[AndycompanyZ]

This is my query that gives me a list of all those delegates who are also SubTeamHeadDelIDs (subteam heads) and gives their SubTeamParentID

 
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])));

Now how can i put this in another query that shows all the delegates scheduled.

[Bob Fitz]

Hi
The query you have created can be placed in another query just as you would a table. If you ultimately want to use the SQL of the first query in the second query (as a sub query SELECT statement) I would first check that it gives the required results, then post the SQL statement of the new query. I'm sure there are people here who will be able to help with this.