A MySQL query syntax does not work in Access

[rtcary]

I'm trying to move a MySQL DB to Access.

This MySQL query generates a syntax error:

SELECT c.CL_ID, c.Busname, c.Contact, c.Addr1, c.Addr2, c.City, c.State, c.Zip, c.Email, c.CL_ChgDate, sp.Lastname, sp.Firstname, a.Description, a.Rate FROM Clients c
LEFT OUTER JOIN Salespersons sp ON c.SP_ID = sp.SP_ID
INNER JOIN Ads a ON a.AD_ID = c.AD_ID
WHERE (a.Description <> 'Unknown')
ORDER BY c.Busname

Is it because of the aliases?

Many thanks...

Todd

[ItsMe]

I think it is a precedence issue. Maybe you can use parenthesis to retrieve the LEFT JOIN prior to the INNER. Or the other way around, depending on your needs.

SELECT c.CL_ID, c.Busname, c.Contact, c.Addr1, c.Addr2, c.City, c.State, c.Zip, c.Email, c.CL_ChgDate, sp.Lastname, sp.Firstname, a.Description, a.Rate 
FROM
(Clients c LEFT OUTER JOIN Salespersons sp ON c.SP_ID = sp.SP_ID) 
INNER JOIN Ads a ON a.AD_ID = c.AD_ID

[rtcary]

Thank you! That did it.

rtcary

[ItsMe]

Cool .....

[rtcary]

To your knowledge, is there some Access documentation that addresses "precedence"? By adding the parentheses to the following query, it works, however without the parentheses, it works in MySQL and SQL Server.

SELECT c.Busname, c.Contact, c.Addr1, c.Addr2, c.City, c.State, c.Zip, c.Email, c.Phone, sp.Lastname, sp.Firstname, a.Description, a.Rate, s.Pay_recd, s.Pay_amt, s.Pay_note, s.SA_ChgDate
FROM ((Sales s
INNER JOIN Clients c ON c.CL_ID = s.CL_ID)
INNER JOIN Salespersons sp ON sp.SP_ID = c.SP_ID )
INNER JOIN Ads AS a ON a.AD_ID = c.AD_ID
ORDER BY c.Busname, sp.Lastname, sp.Firstname

Thank you,

/rtc

[ItsMe]

Ummm, not anything that I am recalling at the moment. If you want to learn the correct syntax for Access SQL, you will likely have to use the tools provided by Access to create queries. Then, use Transact SQL to see how you can push the limits. I know there are resources out there. For Access queries, I think the best options are some free online reference blogs.

As for precedence with Access SQL, I think it is the simple fact that you need to be explicit and cannot be implicit, like other RDBMS may allow for. I have always been of the school of thought to add the few extra keystrokes and be explicit with my code. If anything, it will help when you try to understand things when you revisit your work in the coming years.

[rtcary]

Thank you for the thorough answer.

/rtc

[ItsMe]

Of course. Good luck with your project.