Need help with SQL JOIN's

[Brian Kitt]

I have a database with several 'master' tables and numerous 'support' tables.
I'm trying to create a SQL SELECT statement that will select the 'master'
record and pull in all associated 'support' tables. For some reason, I can't
get ACCESS to allow me to have more than one JOIN per SELECT. I've tried my
SELECT in Microsoft SQL 2000, and it allows me to JOIN more than 1 support
table. ACCESS just keeps giving me a syntax error.

For example

SELECT employee.name, employee.statecode, state.statename, employee.jobcode,
job.description FROM employee INNER JOIN state ON state.statecode =
employee.statecode INNER JOIN job ON job.jobcode = employee.jobcode

I should be able to have both INNER JOIN's, but ACCESS won't seem to allow
it. Any advice?

 

[John Vinson]

I should be able to have both INNER JOIN's, but ACCESS won't seem to allow
it. Any advice?

Access is very picky about parentheses. Try

SELECT employee.name, employee.statecode, state.statename,
employee.jobcode,
job.description FROM (employee INNER JOIN state ON state.statecode =
employee.statecode) INNER JOIN job ON job.jobcode = employee.jobcode

It may be worthwhile "slumming" in the Query Grid for a bit, and going
back to the SQL window to get a flavor of Access' particular dialect
of SQL.

John W. Vinson[MVP]