Solved

SQL syntax problem with multiple JOINs

Posted on 2008-10-23
14
994 Views
Last Modified: 2010-04-21
Hi,

I'm trying to JOIN data from three tables. My SELECT statement seems fine, but I'm getting a "Join expression not supported" error when saving it in Access 07.

I want to pull all records from table "BW new", and then Join the table "Time" on two "BW new" columns and the table "HPTS" on two columns - 1 column from the "Time" table and 1 column from the "BW new" table. Is this possible in Access?

Here is my current syntax after the SELECT statement:

FROM ([BW new] LEFT JOIN [Time] ON ([Time].[Service Ticket Number] = [BW new].[Service Ticket Number]) AND ([Time].[Interaction Number] = [BW new].[Linked Transaction Number])) LEFT JOIN [HPTS] ON ([Time].[Created by (Agent ID/Name)] = [HPTS].[Created by Agent ID]) AND ([BW new].[Linked Transaction Number] = [HPTS].[Interaction ID])
WHERE ((([Time].[Created Time])>"0"));

Any ideas on what I'm doing wrong/what I should do instead?

Thank you

Bill
0
Comment
Question by:billium99
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 13

Expert Comment

by:adraughn
ID: 22789232
Have you tried changing the name of the table 'Time'? Can you post the entire select?
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22789251
FROM ([BW new] LEFT JOIN [Time] ON ([Time].[Service Ticket Number] = [BW new].[Service Ticket Number]) AND ([Time].[Interaction Number] = [BW new].[Linked Transaction Number]))
LEFT JOIN [HPTS] ON ([Time].[Created by (Agent ID/Name)] = [HPTS].[Created by Agent ID]) AND ([BW new].[Linked Transaction Number] = [HPTS].[Interaction ID])
and ((([Time].[Created Time])>"0"));

-- use 'and' instead of 'where'
0
 
LVL 1

Author Comment

by:billium99
ID: 22789320
Adraughn,

I changed the name to Time2 and didn't have any luck. Here is the original select:

SELECT [BW new].[Service Ticket Number], [BW new].[Created Date], [BW new].[Changed Date], [BW new].[Linked Transaction Number], [BW new].[Linked Transaction Type], [BW new].[Serial Number], [Time].[Resp Serv Org], [Time].[Created by (Agent ID/Name)], [Time].[Field5], [BW new].[Product Line], Time.[Created Time], HPTS.[Complete Logic Path], [BW new].[Operating System], [BW new].[Product Number], [BW new].[Warranty Category], [BW new].[Status], [BW new].[Channel], [HPTS].[Product Series], [HPTS].[Product Family], [HPTS].[Created by Agent ID], [BW new].[Created by (Agent ID/Name)], [BW new].[Field39], [BW new].[Field40], [BW new].[Description]

Jamesqu - using AND instead of WHERE gave me the same error.

Thanks for your time!

Bill
0
 
LVL 13

Expert Comment

by:adraughn
ID: 22789368
what is the error you are receiving?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 22789438
you should rename this field:

Created by (Agent ID/Name)

to:

CreatedByAgentIDName
0
 
LVL 13

Expert Comment

by:adraughn
ID: 22789484
[Description] is also a keyword. Change it to descrip.

Try this and if you get an error tell us what it is:

SELECT bn.[Service Ticket Number], bn.[Created Date], bn.[Changed Date],
bn.[Linked Transaction Number], bn.[Linked Transaction Type], bn.[Serial Number],
t.[Resp Serv Org], t.CreatedByAgentIDName, t.[Field5], bn.[Product Line],
t.[Created Time], h.[Complete Logic Path], bn.[Operating System], bn.[Product Number],
bn.[Warranty Category], bn.[Status], bn.[Channel], h.[Product Series],
h.[Product Family], h.[Created by Agent ID], bn.CreatedByAgentIDName,
bn.Field39, bn.Field40, bn.Descrip
FROM [BW new] bn LEFT JOIN Time2 t ON t.[Service Ticket Number] = bn.[Service Ticket Number]
AND t.[Interaction Number] = bn.[Linked Transaction Number] LEFT JOIN HPTS h ON
t.[Created by (Agent ID/Name)] = h.[Created by Agent ID] AND
bn.[Linked Transaction Number] = h.[Interaction ID]
WHERE t.[Created Time] > 0;
0
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
ID: 22789734
Are you able to create the query in the Query Designer?  Start off with just two tables and get that part working before adding the third.  When the three table query runs, then add the WHERE criteria.  Now, right click on the table pane, select SQL View, and post the SQL string back here.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:billium99
ID: 22789749
HI,

The stat6ement above gives me "Syntax error (missing operator) in query expression and then highlight the AND in the first JOIN
0
 
LVL 13

Expert Comment

by:adraughn
ID: 22789783
oops... try this:

SELECT bn.[Service Ticket Number], bn.[Created Date], bn.[Changed Date],
bn.[Linked Transaction Number], bn.[Linked Transaction Type], bn.[Serial Number],
t.[Resp Serv Org], t.CreatedByAgentIDName, t.[Field5], bn.[Product Line],
t.[Created Time], h.[Complete Logic Path], bn.[Operating System], bn.[Product Number],
bn.[Warranty Category], bn.[Status], bn.[Channel], h.[Product Series],
h.[Product Family], h.[Created by Agent ID], bn.CreatedByAgentIDName,
bn.Field39, bn.Field40, bn.Descrip
FROM [BW new] bn LEFT JOIN Time2 t ON t.[Service Ticket Number] = bn.[Service Ticket Number]
AND t.[Interaction Number] = bn.[Linked Transaction Number] LEFT JOIN HPTS h ON
t.[CreatedByAgentIDName] = h.[Created by Agent ID] AND
bn.[Linked Transaction Number] = h.[Interaction ID]
WHERE t.[Created Time] > 0;
0
 
LVL 1

Author Comment

by:billium99
ID: 22789877
OK - here is the SQL that the query builder built with just two tables:

SELECT bn.[Service Ticket Number], bn.[Created Date], bn.[Changed Date], bn.[Closed Date], bn.Channel, bn.[Linked Transaction Number], bn.[Created Week], bn.[Created Year], bn.[Product Line], bn.[Product Number], bn.[Serial Number], bn.Field39, bn.Field40, t.[Created Time], t.[Created by (Agent ID/Name)], t.Field5
FROM bn INNER JOIN t ON (t.[Interaction Number] = bn.[Linked Transaction Number]) AND (bn.[Service Ticket Number] = t.[Service Ticket Number]);

I tried adding the third table and setting the correct JOIN rules in the wizard, but it then told me I had ambiguous JOINS and that I should pick the one to run first and have the second one be part of a second query within my statement. Does that make sense to anyone?

Thanks

Bill
0
 
LVL 1

Author Comment

by:billium99
ID: 22789893
Adraughn,

Same error.

"Syntax error (missing operator) in query expression" and then highlights the AND in the first JOIN

Bill
0
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 250 total points
ID: 22789956
Save your first query with the two tables, then create another query based off of that query that adds in the third table.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22790370
If you cannot create the query in the Query designer, then the logic behind your Joins is flawed.  Does this work:

FROM ([BW new] LEFT JOIN [Time] ON ([Time].[Service Ticket Number] = [BW new].[Service Ticket Number]) AND ([Time].[Interaction Number] = [BW new].[Linked Transaction Number])) LEFT JOIN [HPTS] ON ([Time].[Created by (Agent ID/Name)] = [HPTS].[Created by Agent ID])
WHERE ((([Time].[Created Time])>"0")) AND ([BW new].[Linked Transaction Number] = [HPTS].[Interaction ID])
0
 
LVL 1

Author Closing Comment

by:billium99
ID: 31509345
Thanks for the help! Two separate queries hadn't occured to me since I never do SQL actually inside of Access - that works fine.

Bill
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now