What is wrong with this Microsoft Access query?

Question

Consider this Access query:

SELECT prod_Parts.ID, prod_Parts.Number, prod_Parts.Revision, prod_Parts.Description
FROM prod_JT_Shipping 
INNER JOIN (prod_JobTraveller 
            INNER JOIN prod_Parts
                ON prod_JobTraveller.PartID = prod_Parts.ID) 
    ON prod_JT_Shipping.JT_ID=prod_JobTraveller.ID;

Also this:

 SELECT prod_Parts.ID, prod_Parts.Number, prod_Parts.Revision, prod_Parts.Description 
 FROM prod_Parts;

This error occurs on both:

'is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long

How can this be fixed?

Answer 1

This part here doesn't look right:

INNER JOIN (prod_JobTraveller 
INNER JOIN prod_Parts ON prod_JobTraveller.PartID = prod_Parts.ID) 

If you consolidate that without whitespace it looks like

INNER JOIN (prod_JobTraveller INNER JOIN prod_Parts ON prod_JobTraveller.PartID = prod_Parts.ID) 

Which is not valid SQL.

Answer 2

What happens if you take out the parenthesis?

Answer 3

You have to give this section below an alias:

(prod_JobTraveller 
            INNER JOIN prod_Parts
                ON prod_JobTraveller.PartID = prod_Parts.ID)

For instance, this may work:

(prod_JobTraveller 
            INNER JOIN prod_Parts
                ON prod_JobTraveller.PartID = prod_Parts.ID) prod_JobTraveller

Answer 4

OK. I have simplified query:

SELECT prod_Parts.ID, prod_Parts.Number, prod_Parts.Revision, prod_Parts.Description FROM prod_Parts;

The same error :)

Answer 5

Judging from the error message, you have some invisible control character in the query, that is causing the problem.

Try to retyping the query from scratch, and it will most likely work.

Side note: I find it easier to follow the joins if they are written in this order (i.e. with the ON clause directly following each JOIN):

SELECT
  prod_Parts.ID, prod_Parts.Number, prod_Parts.Revision, prod_Parts.Description
FROM
  (
    prod_JT_Shipping
    INNER JOIN prod_JobTraveller ON prod_JT_Shipping.JT_ID=prod_JobTraveller.ID
  )
  INNER JOIN prod_Parts ON prod_JobTraveller.PartID = prod_Parts.ID