By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Theory of join tables and views...

patjones
Expert 100+
P: 931
Hi all:

My newest project involves creating a small help desk database for our customer service division to use. What will happen is that an employee will call up, explain his/her issue, and obtain a ticket number. After the problem has been investigated, they will be contacted with the resolution, and the ticket will be closed.

So one of the tables in my database, tblTicket, will hold various pieces of information for a given ticket. Two of those pieces of information will be the numeric code for: 1) the agent who received the call, and 2) the agent who resolved the ticket. I am calling these fldReceivedBy and fldResolvedBy respectively; they will both be integers, and they may or may not be the same for a given record.

Then I came around to making the table to hold the agent information, tblCustService. This has fldAgentCode (an integer) and fldAgentName. This is where I got somewhat confused. If the whole process were handled by one person only, then all I would need in tblTicket is one fldAgentCode to describe who handled the problem, and establish a relationship between tblTicket and tblCustService using fldAgentCode as the key. But instead, I'm left with having to figure out a way to relate fldReceivedBy and fldResolvedBy to fldAgentCode.

Up to this point in my database career (about a year and a half long), I've only heard of views and join tables. But it seems like now I may have a need for them. I understand what a join table does and how it works, but I'm not sure it's what I need here. Then I went and read up a little on views...but I'm not sure I understand how a view is really any different from a join (or even a query for that matter).

The bottom line is, what's the difference between a view and a join table, and can either of them help me in my present circumstance? Thanks for reading this laborious explanation, and I look forward to learning about this...

Pat
Dec 13 '07 #1
Share this Question
Share on Google+
21 Replies


JustJim
Expert 100+
P: 407
Hi all:

My newest project involves creating a small help desk database for our customer service division to use. What will happen is that an employee will call up, explain his/her issue, and obtain a ticket number. After the problem has been investigated, they will be contacted with the resolution, and the ticket will be closed.

So one of the tables in my database, tblTicket, will hold various pieces of information for a given ticket. Two of those pieces of information will be the numeric code for: 1) the agent who received the call, and 2) the agent who resolved the ticket. I am calling these fldReceivedBy and fldResolvedBy respectively; they will both be integers, and they may or may not be the same for a given record.

Then I came around to making the table to hold the agent information, tblCustService. This has fldAgentCode (an integer) and fldAgentName. This is where I got somewhat confused. If the whole process were handled by one person only, then all I would need in tblTicket is one fldAgentCode to describe who handled the problem, and establish a relationship between tblTicket and tblCustService using fldAgentCode as the key. But instead, I'm left with having to figure out a way to relate fldReceivedBy and fldResolvedBy to fldAgentCode.

Up to this point in my database career (about a year and a half long), I've only heard of views and join tables. But it seems like now I may have a need for them. I understand what a join table does and how it works, but I'm not sure it's what I need here. Then I went and read up a little on views...but I'm not sure I understand how a view is really any different from a join (or even a query for that matter).

The bottom line is, what's the difference between a view and a join table, and can either of them help me in my present circumstance? Thanks for reading this laborious explanation, and I look forward to learning about this...

Pat
G'Day Pat,
Just clear something up for me before we get stuck in. Are the people who take the calls and the people who resolve the issues drawn from the same group of people? Or is it that you have a bunch of phone operators and a seperate bunch of problem solvers?

Jim
Dec 14 '07 #2

patjones
Expert 100+
P: 931
G'Day Pat,
Just clear something up for me before we get stuck in. Are the people who take the calls and the people who resolve the issues drawn from the same group of people? Or is it that you have a bunch of phone operators and a seperate bunch of problem solvers?

Jim
They are all drawn from the same group of people. In practice, it will be the same person resolving the issue as receiving it most of the time, but not all the time. So I am trying to account for the most general case where the issue gets handed off to a different person after the ticket is logged...
Dec 14 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
Pat,

A View IS just another name for a SELECT query as far as I understand it. SQL Server uses the term "View" for this I believe.
A JOIN table is probably not necessary here. You would simply have two fields in your tblTicket record (fldReceivedBy & fldResolvedBy) which link to the same table (tblCustService) separately. The following SQL snippet gives an example of the sort of thing you would need :
Expand|Select|Wrap|Line Numbers
  1. SELECT Tkt.fldTicket,
  2.        CSRcv.fldAgentName AS RcvName,
  3.        CSRsv.fldAgentName AS RsvName,
  4.        ...
  5. FROM (tblTicket AS Tkt LEFT JOIN tblCustService AS CSRcv ON ...)
  6.      LEFT JOIN tblCustService AS CSRsv ON ...
  7. etc
Dec 14 '07 #4

patjones
Expert 100+
P: 931
Pat,

A View IS just another name for a SELECT query as far as I understand it. SQL Server uses the term "View" for this I believe.
A JOIN table is probably not necessary here. You would simply have two fields in your tblTicket record (fldReceivedBy & fldResolvedBy) which link to the same table (tblCustService) separately. The following SQL snippet gives an example of the sort of thing you would need :
Expand|Select|Wrap|Line Numbers
  1. SELECT Tkt.fldTicket,
  2. CSRcv.fldAgentName AS RcvName,
  3. CSRsv.fldAgentName AS RsvName,
  4. ...
  5. FROM (tblTicket AS Tkt LEFT JOIN tblCustService AS CSRcv ON ...)
  6. LEFT JOIN tblCustService AS CSRsv ON ...
  7. etc
I sort of see what you're saying. Ultimately, the reason I need to do this is to display the agent(s) name(s) on a form in a text box. Suppose I had a textbox, txtReceivedByName. Couldn't I just set the box's data source to something like

Expand|Select|Wrap|Line Numbers
  1.  SELECT fldAgentName 
  2.  
  3. FROM tblCustService
  4.  
  5. WHERE tblCustService.fldAgentCode = tblTicket.fldReceivedBy
  6.  
...and then the same thing for txtResolvedByName except the WHERE clause changes to

Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE tblCustService.fldAgentCode = tblTicket.fldResolvedBy
  3.  
...or am I simplifying this too much?
Dec 14 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
TextBoxes have a ControlSource property, not a RecordSource property.
This means they can be set to a fieldname, but not a query. The form as a whole is set to the query (or SQL) and each bound TextBox uses one of the fields returned in the query as its ControlSource. Hence the slightly more complex SQL structure.
Play with it a little. It's not as complex as it looks first time around. I can clarify any details if you ask.
Dec 14 '07 #6

patjones
Expert 100+
P: 931
TextBoxes have a ControlSource property, not a RecordSource property.
This means they can be set to a fieldname, but not a query. The form as a whole is set to the query (or SQL) and each bound TextBox uses one of the fields returned in the query as its ControlSource. Hence the slightly more complex SQL structure.
Play with it a little. It's not as complex as it looks first time around. I can clarify any details if you ask.
OK. I'll give it a try. I'm just setting the tables up now. I'll probably set the form up on Monday and let you know how it goes. Thanks so much for your input...
Dec 14 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
No problems.
Let us know how you get on :)
Dec 14 '07 #8

patjones
Expert 100+
P: 931
No problems.
Let us know how you get on :)
OK, I think I've digested your SQL query a little better. Essentially, this query creates new virtual tables, CSRcv, and CSRsv - where CSRcv (CSRsv) will hold all the customer service agents who logged (resolved) problems.

Then I can reference the fields RcvName and RsvName in those virtual tables in the Controlsource property for the relevant text boxes on the form...

Am I reading the SQL query the right way?
Dec 15 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
Essentially, yes.
I would describe the virtual tables as aliases or views of the same table, but certainly there would be two recordsets. Both processing through the same actual table, but there could two different records of that table linked in for any one output record. The Received by record, and the Resolved by record.
As they are both LEFT JOINs, it is also possible for either or both to be empty (null).
Dec 16 '07 #10

Jim Doherty
Expert 100+
P: 897
They are all drawn from the same group of people. In practice, it will be the same person resolving the issue as receiving it most of the time, but not all the time. So I am trying to account for the most general case where the issue gets handed off to a different person after the ticket is logged...
Hi Zepp,

Just my two cents worth here if you are using SQL Server given that you mention 'views' look at the user defined function' side of SQL Server as well. It may provide you with some useable features you may consider worthwhile exploring.

The beauty of those being that you can reference the function in the row of a view in much the same way as an Access 'function' 'returns a value to a virtual column matrix display. (your view when graphically displayed is not cluttered up with tables 'basically' and the view can also become 'editable' whereas in conventional SQL joins in cases where any joins are greater than 'one' from the main table in a view it would not be editable because of the SQL in essence not knowing which table to update essentially. I hope I'm making sense here!!)

Example UDF function (Surname and firstname may not be your fieldnames of course but you get the idea in principle I'm sure)

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE FUNCTION dbo.UDF_Person
  3. ( @fldAgentCode int )
  4. RETURNS varchar(255)
  5. AS
  6. BEGIN
  7. RETURN ( SELECT COALESCE (LTRIM(RTRIM(Surname)), '') + ' ' + COALESCE (LTRIM(RTRIM(FirstName)), '') as fldAgentName FROM tblCustService where fldAgentCode=@fldAgentCode )
  8. END
  9.  
In a subsequent 'view' (shown below) or stored procedure the syntax is reduced visually, becomes editable and 'to the eye' does not mention joins which of course it is doing as we understand the process behind it.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT TicketID,fldReceivedBy,dbo.UDF_Person(fldReceivedBy) AS PersonReceiving,fldResolvedBy,dbo.UDF_Person(fldResolvedBy) AS PersonResolving
  3. FROM dbo.tblTicket
  4.  

Regards
Jim :)
Dec 16 '07 #11

patjones
Expert 100+
P: 931
NeoPa:

OK, I'm at the point where I need to implement this plan.

As I mentioned before, I understand your SQL statement as it stands. I guess though that I should write out my SQL statement here. This is the recordsource for the form I use to view the tickets:

Expand|Select|Wrap|Line Numbers
  1.  SELECT tblCaller.*, 
  2.                              tblCaller.fldPDC AS fldPDC_FK, 
  3.                              tblTicket.*, 
  4.                              tblTitles.*, 
  5.                              tblPDC.*, 
  6.                              tblCallSubject.*, 
  7.                              tblTicketStatus.*, 
  8.                              tblCallerType.* 
  9.  
  10. FROM tblTitles INNER JOIN (tblTicketStatus 
  11.                          INNER JOIN (tblPDC 
  12.                          INNER JOIN (tblCallSubject 
  13.                          INNER JOIN (tblCallerType 
  14.                          INNER JOIN (tblCaller 
  15.                          INNER JOIN tblTicket ON tblCaller.fldERN = tblTicket.fldERN) 
  16.                         ON tblCallerType.fldCallerTypeCode = tblTicket.fldCallerTypeCode) 
  17.                         ON tblCallSubject.fldCallSubjectID = tblTicket.fldCallSubjectID) 
  18.                         ON tblPDC.fldPDC = tblCaller.fldPDC) 
  19.                         ON tblTicketStatus.fldTicketStatusID = tblTicket.fldTicketStatusID) 
  20.                         ON tblTitles.fldTitleCode = tblCaller.fldTitleCode;"
  21.  
This is what Access' query builder gives me, and when I paste it into the VB module for the form, it works fine. But of course, it doesn't contain the received by and resolved by customer service agents as we were discussing earlier.

Basically, I haven't studied much SQL yet, so I'm not sure how to implement the LEFT JOINS in the context of my existing SQL code. I understand why my SQL code is structured the way it is, and why yours is structured the way it is. How do I combine them?

I'm going to play around with it. If you have any insight, that would be great.

Regards,

Pat
Dec 21 '07 #12

brightshadow
P: 10
Quick and dirty info on SQL JOINs:

http://en.wikipedia.org/wiki/Join_%28SQL%29

You'll generally want to be concerned with INNER and LEFT JOIN statements, I think. A lot of the other JOIN types have their uses, but are less common.

[Edit: Ah-ha! I found a page that's got Venn diagrams. Those are useful for seeing "how" different JOINs work, except for cross joins, which are insane and nearly useless!]

http://www.codinghorror.com/blog/archives/000976.html
Dec 22 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
Quick and dirty info on SQL JOINs:

http://en.wikipedia.org/wiki/Join_%28SQL%29

You'll generally want to be concerned with INNER and LEFT JOIN statements, I think. A lot of the other JOIN types have their uses, but are less common.

[Edit: Ah-ha! I found a page that's got Venn diagrams. Those are useful for seeing "how" different JOINs work, except for cross joins, which are insane and nearly useless!]

http://www.codinghorror.com/blog/archives/000976.html
Nice links BrightShadow.
We also have an article here (on this site) which gives a bit of an introduction to SQL JOINs (SQL JOINs). It's certainly easier to picture with the diagrams though.
Dec 22 '07 #14

NeoPa
Expert Mod 15k+
P: 31,186
NeoPa:

OK, I'm at the point where I need to implement this plan.

As I mentioned before, I understand your SQL statement as it stands. I guess though that I should write out my SQL statement here. This is the recordsource for the form I use to view the tickets:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCaller.*,
  2.        tblCaller.fldPDC AS fldPDC_FK,
  3.        tblTicket.*,
  4.        tblTitles.*,
  5.        tblPDC.*,
  6.        tblCallSubject.*,
  7.        tblTicketStatus.*,
  8.        tblCallerType.*
  9. FROM tblTitles INNER JOIN
  10.     (tblTicketStatus INNER JOIN
  11.     (tblPDC INNER JOIN
  12.     (tblCallSubject INNER JOIN
  13.     (tblCallerType INNER JOIN 
  14.     (tblCaller LEFT JOIN
  15.      tblTicket ON tblCaller.fldERN = tblTicket.fldERN)
  16.                ON tblCallerType.fldCallerTypeCode = tblTicket.fldCallerTypeCode)
  17.                ON tblCallSubject.fldCallSubjectID = tblTicket.fldCallSubjectID)
  18.                ON tblPDC.fldPDC = tblCaller.fldPDC)
  19.                ON tblTicketStatus.fldTicketStatusID = tblTicket.fldTicketStatusID)
  20.                ON tblTitles.fldTitleCode = tblCaller.fldTitleCode;
This is what Access' query builder gives me, and when I paste it into the VB module for the form, it works fine. But of course, it doesn't contain the received by and resolved by customer service agents as we were discussing earlier.

Basically, I haven't studied much SQL yet, so I'm not sure how to implement the LEFT JOINS in the context of my existing SQL code. I understand why my SQL code is structured the way it is, and why yours is structured the way it is. How do I combine them?

I'm going to play around with it. If you have any insight, that would be great.

Regards,

Pat
Pat,
As your query uses so many tables it certainly does appear complicated. Especially in the FROM clause (with all the JOINs in). The trick is that the parentheses define a recordset. So :
Expand|Select|Wrap|Line Numbers
  1. Table [tblA] is a recordset (a source of records)
  2. Table [tblB] is a recordset
  3. ([tblA] INNER JOIN [tblB] ON [tblA].[Index] = [tblB].[Index]) is also a recordset
Using this understanding, it's possible to add links into an already complicated FROM clause with confidence, however, due to the complexity of this one it's probably better to let Access add the tblTicket table first with a LEFT JOIN and using that SQL as a starting point (as I found out after trying to do it manually for a while). I've found that too many manual changes to SQL structure when it's remotely complicated often end up with small errors that simply cause Access to refuse to process it. In your case the tblTicket table has links to various of the other tables so changing the JOIN type and adding an extra copy of the same table would certainly be fraught.
If you use Access to change the JOIN type for tblTicket in the QBE grid, then you can post the resulting SQL back in here and I'll look to add the extra table for you. Please test the SQL you send me thoroughly though. It must return records before we make any changes.
Dec 22 '07 #15

patjones
Expert 100+
P: 931
Pat,
As your query uses so many tables it certainly does appear complicated. Especially in the FROM clause (with all the JOINs in). The trick is that the parentheses define a recordset. So :
Expand|Select|Wrap|Line Numbers
  1. Table [tblA] is a recordset (a source of records)
  2. Table [tblB] is a recordset
  3. ([tblA] INNER JOIN [tblB] ON [tblA].[Index] = [tblB].[Index]) is also a recordset
Using this understanding, it's possible to add links into an already complicated FROM clause with confidence, however, due to the complexity of this one it's probably better to let Access add the tblTicket table first with a LEFT JOIN and using that SQL as a starting point (as I found out after trying to do it manually for a while). I've found that too many manual changes to SQL structure when it's remotely complicated often end up with small errors that simply cause Access to refuse to process it. In your case the tblTicket table has links to various of the other tables so changing the JOIN type and adding an extra copy of the same table would certainly be fraught.
If you use Access to change the JOIN type for tblTicket in the QBE grid, then you can post the resulting SQL back in here and I'll look to add the extra table for you. Please test the SQL you send me thoroughly though. It must return records before we make any changes.
Yes, that's a pretty cool page brightshadow. I find the example alone very instructive, even with the Venn diagrams aside.

I know this is a long SQL query. I've tried to normalize this database as far as possible, so there are a lot of one-to-many relationships. Is there a way that I can attach the relationships report from Access here? It might provide cleaner perspective...

Anyway, when I use query design view to LEFT JOIN tblTicket with tblCustService using just fldReceivedBy = fldAgentCode, and run it, I get the correct results. In other words, it gives me all the tickets with the correct agent name (fldAgentName) in it's column. That SQL looks like:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblCaller.*, 
  2.                             tblTicket.*, 
  3.                             tblTitles.*, 
  4.                             tblPDC.*, 
  5.                             tblCallSubject.*, 
  6.                             tblTicketStatus.*, 
  7.                             tblCallerType.*, 
  8.                             tblCustService.fldAgentName
  9.  
  10. FROM (tblTitles INNER JOIN 
  11.             (tblTicketStatus INNER JOIN 
  12.             (tblPDC INNER JOIN 
  13.             (tblCallSubject INNER JOIN 
  14.             (tblCallerType INNER JOIN 
  15.             (tblCaller INNER JOIN tblTicket 
  16.  
  17.             ON tblCaller.fldERN = tblTicket.fldERN) 
  18.             ON tblCallerType.fldCallerTypeCode = tblTicket.fldCallerTypeCode) 
  19.             ON tblCallSubject.fldCallSubjectID = tblTicket.fldCallSubjectID)   
  20.             ON tblPDC.fldPDC = tblCaller.fldPDC) 
  21.             ON tblTicketStatus.fldTicketStatusID = tblTicket.fldTicketStatusID) 
  22.             ON tblTitles.fldTitleCode = tblCaller.fldTitleCode) 
  23.             LEFT JOIN tblCustService ON tblTicket.fldReceivedBy = tblCustService.fldAgentCode;
In essence, this is just the same thing as what I had before, but with the LEFT JOIN tagged on to the end of the statement, and like I say, it works great. The issue is, what will happen when I add a second LEFT JOIN for tblTicket.fldResolvedBy? I'll have two fields in tblTicket pointing to one field in tblCustService; I'm not so sure Access' query builder will be able to handle that situation. I'll give it a try and let you know what the results are.

Pat
Dec 22 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
Pat,
I'm sorry, but I can tell from the SQL that you have added the tblTicket table LEFT JOIN links, but without removing the INNER JOIN links first.
I need you either to remove the existing INNER JOIN links first (to tblTicket) OR explain why that is not a good idea in your situation. I'm surprised this SQL actually runs even (You did test it thoroughly first as I asked. It says so in your post).
Now I look again at your post again (the SQL specifically), I see that you have in fact, added tblCustService as a LEFT JOIN instead of tblTicket. What we need is tblTicket extricated from the core of the links then added as a separate LEFT JOIN. Again, let me know if this proves to be a problem.

Merry Christmas.
Dec 23 '07 #17

patjones
Expert 100+
P: 931
Pat,
I'm sorry, but I can tell from the SQL that you have added the tblTicket table LEFT JOIN links, but without removing the INNER JOIN links first.
I need you either to remove the existing INNER JOIN links first (to tblTicket) OR explain why that is not a good idea in your situation. I'm surprised this SQL actually runs even (You did test it thoroughly first as I asked. It says so in your post).
Now I look again at your post again (the SQL specifically), I see that you have in fact, added tblCustService as a LEFT JOIN instead of tblTicket. What we need is tblTicket extricated from the core of the links then added as a separate LEFT JOIN. Again, let me know if this proves to be a problem.

Merry Christmas.
Hi:

Hope you had a great holiday.

I'm starting to get lost. This is simply the SQL that Access gave me after I graphically designed the query in query design view. tblTicket is involved in several one-to-one relationships, as indicated by all the INNER JOINS that connect it to other tables. I wish I could show you the relationships window from Access to get a better idea of how things are set up.

When I say that I've tested it, I mean that I have run the query from design view, and it brings up a table with all the correct results. This means that it gives me every ticket in my database, each with the correct name of the customer service agent who received the call. I just need it to do the same for the customer service agent who resolved the ticket, perhaps through another LEFT JOIN on the end of the SQL?

I haven't inserted this SQL into my VB module yet...

PJ
Dec 26 '07 #18

patjones
Expert 100+
P: 931
After playing around with the SQL code for a while, I have found this to be the statement that works perfectly and does everything I want. I've tested it with several records and it stands up to the tests so far:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblTkt.*, 
  3.                  tblCaller.*, 
  4.                  tblCSRcv.fldAgentName AS fldRcvName, 
  5.                  tblCSRsv.fldAgentName AS fldRsvName, 
  6.                  tblTitles.fldTitle, 
  7.                  tblPDC.fldPDC AS fldPDC_FK, 
  8.                  tblPDC.fldWorkLocation, 
  9.                  tblCallSubject.fldCallSubject, 
  10.                  tblCallerType.fldCallerType, 
  11.                  tblTicketStatus.fldTicketStatus
  12.  
  13. FROM tblTicketStatus INNER JOIN (tblCallerType 
  14.                                      INNER JOIN (tblCallSubject 
  15.                                      INNER JOIN (tblPDC 
  16.                                      INNER JOIN (tblTitles
  17.                                      INNER JOIN (tblCaller 
  18.                                      INNER JOIN ((tblTicket AS tblTkt 
  19.  
  20.                                      LEFT JOIN tblCustService AS tblCSRcv ON tblCSRcv.fldAgentCode = tblTkt.fldReceivedBy)     
  21.                                      LEFT JOIN tblCustService AS tblCSRsv ON tblCSRsv.fldAgentCode = tblTkt.fldResolvedBy) 
  22.  
  23.                                      ON tblCaller.fldERN = tblTkt.fldERN) 
  24.                                      ON tblTitles.fldTitleCode = tblCaller.fldTitleCode) 
  25.                                      ON tblPDC.fldPDC = tblCaller.fldPDC) 
  26.                                      ON tblCallSubject.fldCallSubjectID = tblTkt.fldCallSubjectID) 
  27.                                      ON tblCallerType.fldCallerTypeCode = tblTkt.fldCallerTypeCode) 
  28.                                      ON tblTicketStatus.fldTicketStatusID = tblTkt.fldTicketStatusID
  29.  
The key here is essentially as you pointed out, NeoPa...namely, the aliasing of tblCustService into two different tables tblCSRcv and tblCSRsv based on two different criteria. I started with a simple SQL statement that did only that, and just built on it from there.

Let me know if you have any comments about this. Thanks so much for all your insight...
Dec 27 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
PJ, I think I missed these responses.
What you posted was the SQL version of what you had in the QBE (Query By Example - or Access design view) window. In there the INNER JOINs are set as type one links (default when dragging & dropping) and the LEFT JOINs are converted when you double-click on the link created and select type 2.
What I was trying to say was that until you have a consistent set of INNER JOINs, you won't be able to add the LEFT JOINs. In truth, this is complicated to explain without our both seeing the same screen in front of us.

As it happens, your last post (#19) seems to have the concept down right. You're still using two copies of [tblCustService] rather than [tblTicket] (as I'm sure you said you were after in post #1) but I expect that's because it's actually that that you need rather than tblTicket. Assuming that's right, your last post certainly has the correct concept in the SQL. If I were to be picky, I might mention that it's more usual and easier to interpret if you make the "ON" part of the LEFT JOINs go the other way around, but as the operator is "=" anyway it doesn't make any practical difference. Just more usual and easier to read.

But in essence I would award that last attempt top marks.
Jan 6 '08 #20

patjones
Expert 100+
P: 931
PJ, I think I missed these responses.
What you posted was the SQL version of what you had in the QBE (Query By Example - or Access design view) window. In there the INNER JOINs are set as type one links (default when dragging & dropping) and the LEFT JOINs are converted when you double-click on the link created and select type 2.
What I was trying to say was that until you have a consistent set of INNER JOINs, you won't be able to add the LEFT JOINs. In truth, this is complicated to explain without our both seeing the same screen in front of us.

As it happens, your last post (#19) seems to have the concept down right. You're still using two copies of [tblCustService] rather than [tblTicket] (as I'm sure you said you were after in post #1) but I expect that's because it's actually that that you need rather than tblTicket. Assuming that's right, your last post certainly has the correct concept in the SQL. If I were to be picky, I might mention that it's more usual and easier to interpret if you make the "ON" part of the LEFT JOINs go the other way around, but as the operator is "=" anyway it doesn't make any practical difference. Just more usual and easier to read.

But in essence I would award that last attempt top marks.
What I am discovering is, a central concept to building SQL queries is that when you perform an INNER JOIN, you've essentially got two tables that are now becoming one unit; this is something you pointed out many posts ago and is how I built this one from scratch. When you start from scratch and start putting in the INNER JOINS one by one, it's much easier to see how the whole thing hangs together.

Thanks again for all your help...
Jan 8 '08 #21

NeoPa
Expert Mod 15k+
P: 31,186
No problem at all.
In fact I'm very gratified that you're learning new things and I've helped with that. I steered clear of SQL (preferring the safer QBE) for a long while in Access queries, but then I got into it and one of the fundamental concepts I picked up to help unravel the complexity of it all was exactly what you just put so well there. It still gets quite tricky when dealing with complicated structures, but at least now there is a way to break it down into simpler steps.
Jan 9 '08 #22

Post your reply

Sign in to post your reply or Sign up for a free account.