Link to home
Start Free TrialLog in
Avatar of Bill Henderson
Bill HendersonFlag for United States of America

asked on

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
Avatar of adraughn
adraughn
Flag of United States of America image

Have you tried changing the name of the table 'Time'? Can you post the entire select?
Avatar of jamesgu
jamesgu

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'
Avatar of Bill Henderson

ASKER

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

Created by (Agent ID/Name)

to:

CreatedByAgentIDName
[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;
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
HI,

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

Same error.

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

Bill
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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])
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