SQL Join statement help 2

[guitarzan]

I need help creating a SQL query; I have simplified this as much as possible to illustrate what I don't understand. My Access database has three tables:

Table: WorkLog
wlID
wlDate
wlTask
wlClient - contains a clID
WlAssignedTo - contains an empID

Table: Clients
clID
clName
clTeamLeader - contains an empID
clTeamMember1 - contains an empID
clTeamMember2 - contains an empID
clTeamMember3 - contains an empID

Table: Employees
empID
empName


What I want to do is create a SQL statement that returns records from the WorkLog table that include not only client name and assigned employee (I can do that), but also the team leader and team members for that client (which are defined in the client table). My code, with the SQL statement I'm using is below.

Option Explicit
Dim sSQL As String
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cm As ADODB.Command

Private Sub Command1_Click()
   
   Set Conn = New ADODB.Connection
   Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test1.mdb"
   Set cm = New ADODB.Command
   cm.ActiveConnection = Conn
   cm.CommandType = adCmdText
   
   sSQL = _
      "SELECT " & _
      " WorkLog.wlID," & _
      " WorkLog.wlDate," & _
      " WorkLog.wlTask," & _
      " WorkLog.wlClient," & _
      " WorkLog.wlAssignedTo," & _
      " Clients.clName," & _
      " Clients.clTeamLeader," & _
      " Clients.clTeamMember1," & _
      " Clients.clTeamMember2," & _
      " Clients.clTeamMember3, " & _
      " Employees.empName " & _
      "FROM Clients INNER JOIN " & _
      " (Employees INNER JOIN WorkLog ON Employees.empID = WorkLog.wlAssignedTo) ON Clients.clID = WorkLog.wlClient;"

   cm.CommandText = sSQL
   Set rs = New ADODB.Recordset
   rs.Open cm, , adOpenKeyset, adLockOptimistic
   
   Do While Not rs.EOF
      Debug.Print rs("wlID"), rs("wlDate"), rs("clName"), rs("empName"), rs("clTeamLeader"), rs("clTeamMember1"), rs("clTeamMember2"), rs("clTeamMember3")
      rs.MoveNext
   Loop
   
   rs.Close
   Set rs = Nothing
   Set cm = Nothing
   Conn.Close
   Set Conn = Nothing

End Sub

Result:

 1  12/18/2005    ABC Corp      George   4    1   2   3 
 2  12/19/2005    ABC Corp      George   4    1   2   3 
 3  12/20/2005    DEF Company   George   11   5   6   3 
 4  12/21/2005    GH Inc.       George   10   3   7   13

How can I change my SQL to show empNames instead of empIDs for the Team Leader/Member fields?

- guitarzan

 

[guitarzan]

Thanks to everyone for their suggestions and links! After I get this project working and have some time, I will restructure the database more logically and use it to fill in the gaps in my knowledge.

- guitarzan