Syntax error in JOIN operation (assigning aliases to tables?)

[pmregan]

I know I must be missing something incredibly elementary here, but I can't for the life of me determine where my "syntax error" is coming from. Can anyone help me with this one?

The following query gives me the überhelpful ""Syntax error in JOIN operation" message, and highlights the first "reqts" table that I'm trying to join with my "references" table.

Thanks in advance for any help!

SELECT references.incomingref
, incoming.reqt_text
, outgoing.cRef AS [Child Requirement]
, dispositions.shortdisposition
, outgoing.reqt_text AS [Child Requirement Text]
, outgoing.notes AS [Disposition Notes]
FROM ((
   references
      INNER JOIN reqts AS outgoing
         ON outgoing.reqtID = references.outgoingref
      )
      INNER JOIN reqts AS incoming
         ON incoming.reqtID = references.incomingref
      )
      INNER JOIN dispositions
         ON dispositions.dispositionID = reqts.disposition
WHERE outgoing.disposition = (SELECT dispositionID FROM dispositions WHERE shortdisposition = 'NONCOMPLIANT');

[orange]

You could try this and see if you get the same issue. It should be equivalent to your code. I do see that you have aliased references AS outgoing and that you then do
outgoing.reqtID = references.outgoingref which may be a problem. Just some thoughts on a quick look.

I don't think you can use both the original name and alias names as you have shown, but I haven't tested that.
You might want to start with a basic JOIN and get your alias "syntax" working before going to multiple JOINs.

SELECT
references.incomingref
, incoming.reqt_text
, outgoing.cRef AS [Child Requirement]
, dispositions.shortdisposition
, outgoing.reqt_text AS [Child Requirement Text]
, outgoing.notes AS [Disposition Notes]
FROM
references AS outgoing
,reqts AS incoming
,dispositions
WHERE
outgoing.reqtID = references.outgoingref AND
incoming.reqtID = references.incomingref AND
dispositions.dispositionID = reqts.disposition AND
outgoing.disposition =
(SELECT dispositionID
FROM dispositions
WHERE shortdisposition = 'NONCOMPLIANT');

[pmregan]

Thanks, Orange.

A quick copy/paste didn't seem to resolve the issue, but I haven't had a chance to spend more time on it.

For what it's worth, both joins worked before I tried to do a multiple join, so that's what led me to think that Access might have some funny syntax requirements for joining tables to themselves?

I'll update the thread after my next attempt.

Thanks again,

Patrick