Solved

SQL syntax problem with multiple JOINs

Posted on 2008-10-23
14
995 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

813 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

14 Experts available now in Live!

Get 1:1 Help Now