inner join sytax error, missing operator.

[Gluefoot]

pretty new to creating queries using SQL in Access. i have the following SQL
command but it says I'm missing an operator. I can't figure out how to fix.
probably something simple. can someone help?

SELECT USER_NAME.ASSOCIATE, TASK_TRACKING.IN_TIME, TASK_TRACKING.OUT_TIME,
TASK_TRACKING.TASK_ID
FROM TASK_TRACKING
INNER JOIN USER_NAME
ON TASK_TRACKING.USER_ID = USER_NAME.USER_ID
INNER JOIN TASK
ON TASK_TRACKING.TASK_ID = TASK.TASK_ID
WHERE USER_NAME.BADGE_NUMBER = "011167"

 

[vanderghast]

Try adding parenthesis:


SELECT USER_NAME.ASSOCIATE, TASK_TRACKING.IN_TIME, TASK_TRACKING.OUT_TIME,
TASK_TRACKING.TASK_ID
FROM ( TASK_TRACKING
INNER JOIN USER_NAME
ON TASK_TRACKING.USER_ID = USER_NAME.USER_ID )
INNER JOIN TASK
ON TASK_TRACKING.TASK_ID = TASK.TASK_ID
WHERE USER_NAME.BADGE_NUMBER = "011167"


With Jet, the ( ) are kind of part of the syntax, rather than denoting an
order of execution (a potential problem when using outer join)... even if
sometimes, it works without them!

Vanderghast, Access MVP

 

[Bob Barrows]

pretty new to creating queries using SQL in Access. i have the
following SQL command but it says I'm missing an operator. I can't
figure out how to fix. probably something simple. can someone help?

SELECT USER_NAME.ASSOCIATE, TASK_TRACKING.IN_TIME,
TASK_TRACKING.OUT_TIME, TASK_TRACKING.TASK_ID
FROM TASK_TRACKING
INNER JOIN USER_NAME
ON TASK_TRACKING.USER_ID = USER_NAME.USER_ID
INNER JOIN TASK
ON TASK_TRACKING.TASK_ID = TASK.TASK_ID
WHERE USER_NAME.BADGE_NUMBER = "011167"

Just to reinforce what vanderghast said: I've never seen a Jet query
with a join involving more than two tables work without parentheses.
You're best bet is to create the joins in Design View and switch to SQL
View to see what parentheses were required. With yours, I would say this
would be the minimum:
FROM (TASK_TRACKING
INNER JOIN USER_NAME
ON TASK_TRACKING.USER_ID = USER_NAME.USER_ID)
INNER JOIN TASK
ON TASK_TRACKING.TASK_ID = TASK.TASK_ID

I suspect the explanation is that in Jet, a join is a binary operation
that can involve no more than two table expressions:
table_expression1 join table_expression2 on ...

The parentheses allow the enclosed join expression to be treated as a
single table expression.