SQL Query Missing Operator

Question

I'm getting stuck trying to create a count of occurrences of every Filename from MainTable in myQuery. With Access (yes I know) complaining about a missing operator in the first join, which does work if I remove the second.

I did try to simplify this to cut down on reading. As far as I can there isn't a missing operator. Let me know if there's a better way to do this or if you can spot the problem.

    SELECT DISTINCT
      mainTable.Filename,
      mainTable.Link,
      otherTable.Field,
      qryC.Total           
    FROM mainTable
    LEFT JOIN otherTable
      ON mainTable.number = otherTable.position_nbr
    LEFT JOIN 
      (SELECT Filename, Count(*) As 
         Total FROM otherQuery
         GROUP BY Filename
       ) As qryC
      ON mainTable.Filename = qryC.Filename
    WHERE (((mainTable.Filename) IS NOT NULL
    OR (mainTable.Filename) <> ""));

Gordon Linoff · Accepted Answer · 2017-09-05 14:25:54Z

2

MS Access needs parentheses in the FROM clause:

FROM (mainTable LEFT JOIN
      otherTable
      ON mainTable.number = otherTable.position_nbr
     ) LEFT JOIN 
     (SELECT Filename, Count(*) As Total
      FROM otherQuery
      GROUP BY Filename
     ) As qryC
     ON mainTable.Filename = qryC.Filename

answered Sep 5, 2017 at 14:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Segfaulter Over a year ago

Thank you, I've been trying to figure out why this was happening for about an hour and half. I hate that this is the answer

Collectives™ on Stack Overflow

SQL Query Missing Operator

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related