SQL Query syntax giving Access problems (1 Viewer)

[DocNice]

Can someone tell me where I might be going wrong here. The following query works in SQL, but somewhere in the LEFT JOINS area, Access gets a little confused and says I'm missing an operator.

SELECT Tariffs.TariffCPUCID, Tariffs.TariffID, AdviceLetters.ALCPUCID, Tariffs.ALID, Tariffs.ScheduleID, Schedules.SheetTitle, AdviceLetters.[Filing Date], Tariffs.[C&E], SheetsCancelling.CancellingID, SheetsCancelling.CancellingCPUCID
FROM Tariffs LEFT JOIN Schedules ON Tariffs.ScheduleID = Schedules.ScheduleID LEFT JOIN AdviceLetters ON Tariffs.ALID = AdviceLetters.ALID LEFT JOIN SheetsCancelling ON SheetsCancelling.TariffID = Tariffs.TariffID
WHERE Tariffs.Type="E"
ORDER BY Tariffs.TariffCPUCID DESC;

 

[RV]

Access comes with a different SQL...

You need to place two of your JOINS between brackets.
Which, I can't tell as I don't know which type of relationships you've got between your tables.

Build the query in Access in query design mode.
Access then will come up with the brackets by itself.

RV

 

[mresann]

In the Left Join clauses, you need to prioritize join structures with parantheses. Change the FROM clause you have highlighted to the following:

FROM ((Tariffs LEFT JOIN Schedules ON Tariffs.ScheduleID = Schedules.ScheduleID) LEFT JOIN AdviceLetters ON Tariffs.ALID = AdviceLetters.ALID) LEFT JOIN SheetsCancelling ON SheetsCancelling.TariffID = Tariffs.TariffID

Note the placement of the parantheses, which is the only thing different than your example.

 

[DocNice]

Hey guys. Thanks for the help, that worked. Unfortunately, this didn't solve my long-term problem of improving query performance, but it did get the query to work. When I use the language Access comes up with on its own, the query is extremely complicated, and I think it's causing the query to run on the local machine rather than the SQL server. At least that's what Access help tells me. The query runs instantly on the SQL query analyzer, but takes nearly a minute through Access (linked to SQL).

Anyway, I digress. Thanks for your help. This place is awesome!

 

[FoFa]

You could create it as a PASS THROUGH query so it runs on the Sql Server

 

[DocNice]

Thanks FoFa! Man, you have no idea how long I was trying to solve that problem. Next time I'm in Texas I'll have to buy you a beer!