Solved

SQL syntax problem with multiple JOINs

Posted on 2008-10-23
14
993 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

12 Experts available now in Live!

Get 1:1 Help Now