Post reply

Access 2000 and ANSI-92 Syntax

  • May 13, 2005 at 3:57 pm

    #67335

    I created the following query to make it ANSI-92/99 compliant. But it would not work in Access2000 which is supposed to be ANSI 92 compliant.

    I check 2 books and online and my syntax seems to be correct.

    When I run the following query i get this error: "Syntax error (missing operator) in query expression et.employeetestid=cc.employeetestid

    INNER JOIN completed_categories cc ON cc.ccid = cq.ccid INNER JOIN questions q ON cq.qid = q.qid INNER JOIN responses r ON cq.cqid = r.cqid"

    SELECT cq.cqid, cq.qid, q.question, r.responseid, r.answerid

    FROM completed_questions cq

    INNER JOIN employeetests et ON et.employeetestid=cc.employeetestid

    INNER JOIN completed_categories cc ON cc.ccid = cq.ccid

    INNER JOIN questions q ON cq.qid = q.qid

    INNER JOIN responses r ON cq.cqid = r.cqid

    WHERE et.empid = 56

     AND cc.categoryid = 2

    If i just use the word JOIN, it has a heart attack.

    I used the Query Design View using the same or similar criteria and this is what Access produced. I thought i would look at what access produced to see what it wanted or what i was doing wrong. But i don't understand this.

    SELECT Completed_Questions.CQID, Completed_Questions.QID, Questions.QID, Responses.ResponseID, Answers.AnswerID

    FROM ((Questions

    INNER JOIN Answers ON Questions.QID = Answers.QID)

    INNER JOIN (EmployeeTests

    INNER JOIN (Completed_Categories

    INNER JOIN Completed_Questions ON Completed_Categories.CCID = Completed_Questions.CCID) ON EmployeeTests.EmployeeTestID = Completed_Categories.EmployeeTestID) ON Questions.QID = Completed_Questions.QID)

    INNER JOIN Responses ON (Completed_Questions.CQID = Responses.CQID) AND (Answers.AnswerID = Responses.AnswerID)

    WHERE (([completed_categories].[categoryid]=2) AND ([employeetests].[empid]=56));

    I looks to me like it is chained together. I don't understand this syntax. it looks more complicated than mine. I wonder why my query will not run in access.

    Thanks

  • May 17, 2005 at 10:40 am

    #559313

    thanks guys! i figured out that Access requires parathesis in the query while it is not necessary for the old ANSI-89 standard it appears to be necessary for its ANSI-92 standard. i also understand that these parathesis are not required in SQL server2000

    Here is the correction.

    FROM ((((completed_questions cq

    INNER JOIN employeetests et ON et.employeetestid=cc.employeetestid)

    INNER JOIN completed_categories cc ON cc.ccid = cq.ccid)

    INNER JOIN questions q ON cq.qid = q.qid)

    INNER JOIN responses r ON cq.cqid = r.cqid)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply