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

 

[BobRodes]

Well, part of your problem is that you have violated the first normal form. This isn't an absolute problem; if you have three and only three team members and one and only one team leader all the time, then it might make sense. However, you have to join to the employees table once for each of the team members the way you have it here, if you want to get the employee names.

You might want to consider breaking the teams out into another table.

HTH

Bob

 

[gmmastros]

You'll need to add the employee table 3 more times. Each time you add it, give it an alias (Like TeamMember1, TeamMember2, etc...) BTW, Access's parenthesis always seem to give me a problem, so you may need to clean this up a bit. I suggest you copy/paste this to an access query window and get it running there first.

SELECT WorkLog.wlID,
       WorkLog.wlDate,
       WorkLog.wlTask,
       WorkLog.wlClient,
       WorkLog.wlAssignedTo,
       Clients.clName,
       Clients.clTeamLeader,
       Clients.clTeamMember1,
       TeamMember1.empName As TeamMember1,
       Clients.clTeamMember2,
       TeamMember2.empName As TeamMember2,
       Clients.clTeamMember3, 
       TeamMember2.empName As TeamMember2,
       Employees.empName 
FROM Clients 
     INNER JOIN 
     (Employees INNER JOIN WorkLog ON Employees.empID = WorkLog.wlAssignedTo) ON Clients.clID = WorkLog.wlClient
     Inner Join 
     (Employees As TeamMember1 On Clients.clTeamMember1 = Employees.empId)
     Inner Join 
     (Employees As TeamMember2 On Clients.clTeamMember2 = Employees.empId)
     Inner Join 
     (Employees As TeamMember3 On Clients.clTeamMember3 = Employees.empId)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

By the way, I agree with Bob regarding the 1st normal form for the database structure.

If you've never heard the term before, I urge you to look at this article, it's a good starting point.

http://databases.about.com/od/specificproducts/a/normalization.htm

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[guitarzan]

Thanks for the responses!

Bob, I agree that the team members should really be in a separate table. However in this instance, yes, there will always be one and only one team leader, and member1, 2, and 3 fields may be filled in or left blank. Unless creating a separate table would make my life a lot easier in the short term, I'd like to keep it as is.

George, I think my main weakness is in wading through long JOIN statements... as a matter of fact, I'm getting an error in yours and can't figure out how to fix it! The error I get is "Syntax Error (missing operator) in query expression 'Clients.clID = WorkLog.wlClient Inner Join (etc etc). Do you see what I'm missing?

SELECT WorkLog.wlID,
       WorkLog.wlDate,
       WorkLog.wlTask,
       WorkLog.wlClient,
       WorkLog.wlAssignedTo,
       Clients.clName,
       Clients.clTeamLeader,
       Clients.clTeamMember1,
       TeamMember1.empName As TeamMember1,
       Clients.clTeamMember2,
       TeamMember2.empName As TeamMember2,
       Clients.clTeamMember3, 
       TeamMember3.empName As TeamMember3,
       Employees.empName 
FROM Clients 
     INNER JOIN 
     (Employees INNER JOIN WorkLog ON Employees.empID = WorkLog.wlAssignedTo) ON Clients.clID = WorkLog.wlClient
     Inner Join 
     (Employees As TeamMember1 On Clients.clTeamMember1 = Employees.empId)
     Inner Join 
     (Employees As TeamMember2 On Clients.clTeamMember2 = Employees.empId)
     Inner Join 
     (Employees As TeamMember3 On Clients.clTeamMember3 = Employees.empId)

 

[gmmastros]

I REALLY dislike the way Access forces you to use parenthesis. I'm much better at SQL Server, so you'll have to forgive me. Try this...

SELECT 	WorkLog.wlID,
		WorkLog.wlDate, 
		WorkLog.wlTask, 
		WorkLog.wlClient, 
		WorkLog.wlAssigedTo, 
		Clients.clName, 
		Clients.clTeamLeader, 
		Employee.empName As TeamLeader, 
		Clients.clTeamMember1, 
		Employee_1.empName As TeamMember1, 
		Clients.clTeamMember2, 
		Employee_2.empName As TeamMember2, 
		Clients.clTeamMember3, 
		Employee_3.empName As TeamMember3
FROM 	(WorkLog 
		INNER JOIN Employee ON WorkLog.wlAssigedTo = Employee.empId) 
		INNER JOIN (((Clients 
			INNER JOIN Employee AS Employee_1 ON Clients.clTeamMember1 = Employee_1.empId) 
		INNER JOIN Employee AS Employee_2 ON Clients.clTeamMember2 = Employee_2.empId) 
		INNER JOIN Employee AS Employee_3 ON Clients.clTeamMember3 = Employee_3.empId) 
			ON WorkLog.wlClient = Clients.clId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Jasen]

I would reorder that join to make more sense.

SELECT WorkLog.wlID,
       WorkLog.wlDate,
       WorkLog.wlTask,
       WorkLog.wlClient,
       WorkLog.wlAssignedTo,
       Clients.clName,
       Clients.clTeamLeader,
       Clients.clTeamMember1,
       TM1.empName As TeamMember1,
       Clients.clTeamMember2,
       TM2.empName As TeamMember2,
       Clients.clTeamMember3,
       TM3.empName As TeamMember3,
       Employees.empName
FROM WorkLog
INNER JOIN Clients ON clients.clID = WorkLog.wlClient
Inner Join Employees As TM1 On Clients.clTeamMember1 = TM1.empId
Inner Join Employees As TM2 On Clients.clTeamMember2 = TM2.empId
Inner Join Employees As TM3 On Clients.clTeamMember3 = TM3.empId

This code would work for SQL server, I think it should work the same in Access, but it uses an annoying implementation of SQL, so you may need to tweak it.

 

[Jasen]

sorry George, didn't see you sneak in there before I posted.
Looks like we had the same general idea though.

 

[gmmastros]

jasen,
I'm with you on the SQL Server side. The parenthesis really mess me up, especially since they really aren't needed (by SQL Server) at least.

What I did was...

I opened a blank Access database, created the 3 tables, and then created a Query in design view. I added the employee table 4 times (once for team leader and 3 more times for the team members). Once I got it working, I went to the SQL view and copy/pasted the code here.

If I created the tables properly, then the code I pasted should work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

>> there will always be one and only one team leader, and member1, 2, and 3 fields may be filled in or left blank.

Since the team members are optional, you should change from inner join to left join for the three extra employee tables.

SELECT     WorkLog.wlID,
        WorkLog.wlDate, 
        WorkLog.wlTask, 
        WorkLog.wlClient, 
        WorkLog.wlAssigedTo, 
        Clients.clName, 
        Clients.clTeamLeader, 
        Employee.empName As TeamLeader, 
        Clients.clTeamMember1, 
        Employee_1.empName As TeamMember1, 
        Clients.clTeamMember2, 
        Employee_2.empName As TeamMember2, 
        Clients.clTeamMember3, 
        Employee_3.empName As TeamMember3
FROM     (WorkLog 
        INNER JOIN Employee ON WorkLog.wlAssigedTo = Employee.empId) 
        INNER JOIN (((Clients 
            Left JOIN Employee AS Employee_1 ON Clients.clTeamMember1 = Employee_1.empId) 
        Left JOIN Employee AS Employee_2 ON Clients.clTeamMember2 = Employee_2.empId) 
        Left JOIN Employee AS Employee_3 ON Clients.clTeamMember3 = Employee_3.empId) 
            ON WorkLog.wlClient = Clients.clId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

<and member1, 2, and 3 fields may be filled in or left blank
In other words, you're violating the first normal form.
< I think my main weakness is in wading through long JOIN statements
These two go together!!

<Since the team members are optional, you should change from inner join to left join for the three extra employee tables

Otherwise, you'll only get records that have all three filled.

HTH

Bob

 

[guitarzan]

Excellent! You folks are the best!

To maybe help me get a better grip on how to do this stuff for myself, I changed the indents to your code (I extended it a bit, added Employee_L to handle the Team Leader field).

What I'm having trouble "getting" when I create or add to these join statements myself is what order the stuff goes in, or for example, why the joins towards the bottom are nested the way they are, while the first join is not. Is there an easy way to explain that??? (Hopefully I'm making some sense!)

FROM 
(
   WorkLog INNER JOIN Employees ON ...
) 
INNER JOIN 
(
   (
      (
         (
            Clients LEFT JOIN Employees AS Employee_1 ON ...
         )
         LEFT JOIN Employees AS Employee_2 ON ...
      ) 
      LEFT JOIN Employees AS Employee_3 ON ...
   ) 
   LEFT JOIN Employees AS Employee_L ON ...
) 
ON ...

 

[dilettante]

This thread might be more appropriate in Visual Basic Databases or Access and Jet Queries.

 

[Jasen]

What I'm having trouble "getting" when I create or add to these join statements myself is what order the stuff goes in, or for example, why the joins towards the bottom are nested the way they are, while the first join is not. Is there an easy way to explain that???
It's an Access thing. Other versions of SQL generally don't require parens and especially nesting joins like that. (Of course that depends completely upon the RDBMS being used) Makes things a lot more confusing than they need to be. I *hate* dealing with Access DB's, always try to steer clients towards MSDE, or now 2005 Express. Free SQL server goodness for the masses.

 

[guitarzan]

Thanks jasen, so it's not just my thick skull For future apps with this company I will look into other options besides Access.

Bob, thanks for the suggestions. Looking back, yea I should probably have a Member1 table, Member2 table, and a Member3 table which link employee names with client names. However, I'm not sure how that makes my join statements any less complicated... but I'm still figuring this stuff out later I will post a more direct question in the Visual Basic(Microsoft) Databases forum.

Thanks to all!

 

[gmmastros]

guitarzan

If I understand Bob correctly, then you misunderstood him.

You should not have 3 tables for each of the 3 team members. You should have a single table TeamMember.

This table would have the following structure.

TeamMember
---------
ClientId
empId

There would be a 1 to many relationship on this table with the Clients table. Meaning, for each 1 record in the clients table, there can be many records in this table.

With your current database structure, you can only accomodate 3 team members for a single client. With the table structure I show here, you can have 20 team members for a client if you wanted to.

As for posting in the wrong forum...

Don't be too concerned about it (for this question). It happens all the time. There are hundreds of forums at tek-tips. By posting your question in the correct forum, you maximize your chance for getting a quick and accurate response. Some of the forums have a little overlap (like VB 5 & 6 vs Visual Basic Databases), so it's not too surprising that people post in the wrong forum.

At the top left corner of this web page, there is a picture of a horse. Below that, there are links for 'Browse Forums' and another for 'Forum List'. I encourage you to look through those links.

Hopefully this will help with future posts.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[guitarzan]

Ahhh OK... now I understand... "Member1/2/3" are actually lousy field names (I didn't create them!) They refer to the Team Member who is responsible for doing work for a given client. Member1 is the primary contact, and they will be assigned the work for that client, unless he/she is overloaded, then Member2 gets it, or Member3 etc. And Member1 gets notified of status changes to "their clients" that other Members are working on.

So I didn't think a single table would work, since people are not just members of a team... there is significance to whether someone is Member1 as opposed to Member2 or 3 etc.

BUT thinking out loud makes me see how, while this works great today, leaves no room for expansion... Better might be a single table as you suggest, with
ClientId
empId
Rank (for lack of a better word)

 

[gmmastros]

Now you're getting it. And I'm getting it too.

Since this is more of a workflow sorta thing, you can now expand your functionality by adding start time and end times to the new table. That way, when someone begins working on a project, you update the start time, when they are done, you update the end time. Now you can write reports showing duration, or who has pending work (because they have a start time but no end time, etc..).

To accomplish this with your original structure, you would have to add 6 new fields (start time and end time for each team member).

Or, better yet. You could add an additional table, like this...

TeamMember
----------
ClientId
empId
Rank

EmployeeTime
------------
ClientId
empId
StartTime
EndTime

Again, another 1 to many relationship between the TeamMember table and the EmployeeTime table. So each team member could log the hours spent working for a client. Each team member could have multiple records in this table to indicate several time intervals when that employee was working on the client's project. You can then calculate durations, sum them to get totals. Create weekly reports for the clients, etc...

This is why proper database normalization is so important. With a properly designed database, you have more flexibility to enhance your app with mroe functionality.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Yes, george, you understood me correctly. Furthermore, your last post explains very clearly.

For an example from theory that closely parallels what you've been doing, guitarzan, check

http://databases.about.com/od/specificproducts/l/aa1nf.htm.

It has more material on the kinds of problems you can run into.

Here's a little background in database theory. From a theoretical standpoint, you have "entities." An instance of an entity is a table (typcially). Now, your problem has two types of entities, "kernel" entities and "associative" entities. (There is also the "characteristic" entity, usually expressed as a "lookup table".)

Kernel entities can be described as "stuff," as in stuff you're trying to keep track of, the reason that you're building your database to begin with. Your "stuff" is Clients and Employees. Now, stuff relates to other stuff; in your case, clients relate to employees.

The ideal relation is "one to many." "Mothers" and "children" are examples of kernel entities that are in a one to many relationship: children each have one mother, mothers may have many children.

However, often the kernel entities are in "many to many" relationship with one another. In your case, a client may associate with a number of employees, and an employee may associate with a number of clients. The way to resolve this is to derive an "associative entity", which is always in one to many relationship with each of the kernel entities in many to many relationship. It's often hard to find a name that makes real sense for this type of entity/table. In your case, george's name is fine. You could call it "jobs" maybe, too. A single "job" is an employee working at a client, and all the fields in the table (maybe start date, end date, whatever) would have to do with that employee's assocation with that client.

To give one more example that I often use: we have a need to handle the business of a lending library. (We'll keep it simple and leave fines and room reservations and all of that out of it.) We have two kernel entities: books and members. A book can be borrowed by many members (since we're keeping track of all the loans in the library, the fact that a book can only be borrowed by one member at a time isn't relevant here), and a member can borrow many books. So, we have a members table with all the information about members, a books table with all the information about books, and a loans (an unusually obvious name for an associative entity) table that keeps track of individual loans. A loan is for one book, so one to many with books, and also a loan is for one member, so one to many with members.

So, a good basic way to handle data structuring is to make everyting relate in terms of one to many relationships. This will give you 95% of the optimal way to store data.

HTH

Bob

 

[johnwm]

You will find a good basic guideline on forum usage in faq222-2244.

An excellent starting point for database design is here:

http://www.alphabetcitydataworks.com/fundamentals.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller