query problem (1 Viewer)

[eugzl]

Hi All.
I created query:
see attached file query_A1.txt
When I ran I got error message:

I tried to find a problem by cut code related for Execution2 and Execution3. But leave code related for Execution1 - see attached file query_A2.txt. That part of query works fine. Then copy and paste whole code of query to SQL Server just adjust syntax, - see attachment file query_SQL.txt. That code also works okey. I'm thinking the problem possible in syntax of Access query. I will appreciate if someone help to fix the problem.
Thanks

 

[The_Doc_Man]

A "syntax error (missing operator)" usually means that you have two text items (not usually special symbols) separated by a space or a new-line character. The two items are not usually keywords for SQL because they likely could be table or field names. This could ALSO be caused by a missing comma. Other less likely reasons are possible.

I looked at the files but didn't see anything obvious so I can't offer a correction like you asked. HOWEVER, I did notice in the embedded image that the error processing calls out this string:

ListJobs.ListJob_ID = Execution1.ListJob_ID LEFT JOIN (

It occurs to me that Access will usually report the first error it sees. It sees an error after the Execution1.ListJob_ID Left JOIN ( sub-string which ends with a parenthesis. If that is where the query parser stopped, it thinks the problem becomes recognizable there. I might look for unbalanced parentheses following that string subset. It is a complex enough query that I didn't see it a specific error, but that is probably where your problem resides.

I've also got to observe that you built one helluva JOIN there. Oh, it's conceptually legal - but it will be a complex join to manage.

 

[eugzl]

Hi The_Doc_Men. Thanks for reply.
I found the problem. In Access query each JOIN statement must have open paraeneses after FROM statement and close paraeneses after JOIN statement, accept last one.

 

[The_Doc_Man]

Technically, parentheses are not required for JOIN clauses at all. However, parentheses ARE helpful in organizing the various JOIN clauses - of which you have a few. One thing that IS required is that if you DO use parentheses, they must balance. But according to the language standard, a JOIN can be made without using a parenthesis pair at all. (I'm using the ANSI SQL 1999 standard.)

 

[cheekybuddha]

You haven't nested Execution1, Execution2, Execution3 correctly.

Access nesting of joins is the most annoying thing with its SQL.

Try:

SELECT
  j.JobT_ID,
  j.JobName,
  j.JobID,
  Left(c.Category,3) AS Cat,
  Execution1.Status AS Status1,
  Execution1.CompBy AS CompBy1,
  Execution1.QA AS QA1,
  Execution1.MCompDate AS CompDate1,
  Execution2.Status AS Status2,
  Execution2.CompBy AS CompBy2,
  Execution2.QA AS QA2,
  Execution2.MCompDate AS CompDate2,
  Execution3.Status AS Status3,
  Execution3.CompBy AS CompBy3,
  Execution3.QA AS QA3,
  Execution3.MCompDate AS CompDate3
FROM (
  (
    (
      (
        (
          (
            (
              Jobs j
              LEFT JOIN Categories c
                    ON j.Category_ID = c.Category_ID
            )
            LEFT JOIN ListJobs lj
                  ON j.JobT_ID = lj.JobT_ID
          )
          LEFT JOIN ListGroups lg
                ON lj.ListGroup_ID = lg.ListGroup_ID
        )
        LEFT JOIN Lists l
              ON lg.List_ID = l.List_ID
      )
      LEFT JOIN (
        SELECT
          me.ListJob_ID,
          me.ExecNo,
          s.Status,
          e1.Init AS CompBy,
          e2.Init AS QA,
          me.MCompDate
        FROM (
          (
            (
              MonthlyExecs me
              LEFT JOIN Status s
                    ON me.Status_ID = s.Status_ID
            )
            LEFT JOIN Employees AS e1
                  ON me.Emp_Id = e1.Emp_ID
          )
          LEFT JOIN Employees AS e2
                ON me.QA_Id = e2.Emp_ID
        )
        WHERE MonthlyExecs.ExecNo = 1
      ) AS Execution1
            ON lj.ListJob_ID = Execution1.ListJob_ID
    )
  )
  LEFT JOIN (
    SELECT
      me.ListJob_ID,
      me.ExecNo,
      s.Status,
      e1.Init AS CompBy,
      e2.Init AS QA,
      me.MCompDate
    FROM (
      (
        (
          MonthlyExecs me
          LEFT JOIN Status s
                ON me.Status_ID = s.Status_ID
        )
        LEFT JOIN Employees AS e1
              ON me.Emp_Id = e1.Emp_ID
      )
      LEFT JOIN Employees AS e2
            ON me.QA_Id = e2.Emp_ID
    )
    WHERE MonthlyExecs.ExecNo = 2
  ) AS Execution2
        ON lj.ListJob_ID = Execution2.ListJob_ID
)
LEFT JOIN (
  SELECT
    me.ListJob_ID,
    me.ExecNo,
    s.Status,
    e1.Init AS CompBy,
    e2.Init AS QA,
    me.MCompDate
  FROM (
    (
      (
        MonthlyExecs me
        LEFT JOIN Status s
               ON me.Status_ID = s.Status_ID
      )
      LEFT JOIN Employees AS e1
             ON me.Emp_Id = e1.Emp_ID
    )
    LEFT JOIN Employees AS e2
           ON me.QA_Id = e2.Emp_ID
  )
    WHERE MonthlyExecs.ExecNo = 3
) AS Execution3
      ON lj.ListJob_ID = Execution3.ListJob_ID
WHERE l.ListName = 'Monthly';

If that still fails then save the execution queries as individual queries and join them using the QBE.

 

[cheekybuddha]

Technically, parentheses are not required for JOIN clauses at all

They are required for multiple joins in JET/ACE SQL

 

[The_Doc_Man]

They are required for multiple joins in JET/ACE SQL

The references I found either are silent on the subject or they SUGGEST but do not REQUIRE them. I would very strongly agree with you that their use is wise and highly recommended. So far I have references from ANSI, Microsoft SQL, and a commercial SQL generic reference. However, if you have a reference that states there is an actual requirement specific to JET/ACE, I would be glad to add it to my library.

 

[Pat Hartman]

It is Jet/ACE SQL that requires the parentheses

 

[cheekybuddha]

I would very strongly agree with you that their use is wise and highly recommended.

I would argue that parentheses and the 'nesting' of joined tables is neither wise nor recommended!

Unfortunately, in native Access, they are mandatory when joining more than two tables. It's one of my biggest bugbears with Access SQL (Jet/ACE).

I mean, look at the SQL I posted: there are seven opening brackets before even naming the first table!

Trying to keep them matched and in the right place gives me palpitations!

 

[Pat Hartman]

Trying to keep them matched and in the right place gives me palpitations!

And that's why I use QBE Although it is rare that I need a query with that many joins.

 

[The_Doc_Man]

Not to mention that using the query builder grid automatically dooms you to extra parentheses. Worse than bloody LISP.

 

[Pat Hartman]

But it ONLY MATTERS if you look at the SQL string If you don't paste the SQL String into your code or look at it when you look at the query, what do you care? I know for some folks here, just knowing what a mess Access makes of their beautifully formatted string gives them palpitations but I've learned to live with it. I use the QBE because it saves me work and I only use embedded SQL in ONE SPECIFIC situation. That situation is when the SQL is actually dynamic and I need to build it or modify it with VBA. Believe me, back in the early 90's when I took up Access, getting past the mess Access made of my own beautifully formatted SQL Strings (I'd been writing SQL for 20 years by that time and it was ALWAYS embedded in COBOL) was the second hardest thing for me as I adopted this new tool. The hardest was actually acclimating to the event model and recognizing that I was no longer in control. Access was in control and my code was only a subroutine. The QBE was a tool I had been dreaming about for 20+ years. In my dreams, it worked the way I wanted it to work and it didn't mess up my beautifully formatted strings but sometimes your prince is actually a frog.

There is a trick to get past this but I only bother with it in specific situations. I occasionally have queries with complex where clauses and I reallllllly don't want Access to help me by rewriting them - So - fix the SQL String to remove all the extraneous parentheses and even format it a little. Then DO NOT switch to QBE view EVER AGAIN. Save in SQL view and Access won't mess with your head or eyeballs. To make sure I can get back to the string I want, I keep a table of query strings for just this purpose. That way, if I lose my mind and switch to QBE view and accidentally save, I have a recovery path.