Access Query Problem

[jacobpressures]

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));


It 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 work in access.

Thanks

 

[PHV]

And this (partial) ?
FROM (((completed_questions cq
INNER JOIN completed_categories cc ON cc.ccid = cq.ccid)
INNER JOIN employeetests et ON et.employeetestid=cc.employeetestid)
INNER JOIN questions q ON cq.qid = q.qid)
INNER JOIN responses r ON cq.cqid = r.cqid


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jacobpressures]

Thanks guys, this was great!!!! It helped a lot! Too bad such little things costs days and hours finding out. I mean doing it in Access. ANSI-89 syntax didn't require this. The reply itself was quite timely and i greatly appreciate it.