SQL syntax problem with multiple JOINs

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
LVL 1
billium99Asked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
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
 
adraughnCommented:
Have you tried changing the name of the table 'Time'? Can you post the entire select?
0
 
jamesguCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
billium99Author Commented:
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
 
adraughnCommented:
what is the error you are receiving?
0
 
adraughnCommented:
you should rename this field:

Created by (Agent ID/Name)

to:

CreatedByAgentIDName
0
 
adraughnCommented:
[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
 
billium99Author Commented:
HI,

The stat6ement above gives me "Syntax error (missing operator) in query expression and then highlight the AND in the first JOIN
0
 
adraughnCommented:
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
 
billium99Author Commented:
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
 
billium99Author Commented:
Adraughn,

Same error.

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

Bill
0
 
adraughnConnect With a Mentor Commented:
Save your first query with the two tables, then create another query based off of that query that adds in the third table.
0
 
GRayLCommented:
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
 
billium99Author Commented:
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
All Courses

From novice to tech pro — start learning today.