Select statement (1 Viewer)

[afortney]

I can get this statement to work in SQL Server but will not work in Access:

SELECT AVG(a.Answer) AS avgscore, t.Category_name,
p.Project_id
FROM TEAM_MEMBERS tm INNER JOIN
PROJECT p ON tm.Project_id = p.Project_id INNER JOIN
ANSWER a INNER JOIN
PAGE t ON a.Page_id = t.Page_id ON
tm.Member_id = a.Member_id
GROUP BY p.Project_id, t.Category_name

 

[The_Doc_Man]

Don't know why that doesn't work, but try this thought: Cascade some queries to perform the inner joins one at a time. Choose the most restrictive join first for performance reasons.

In other words, define X = A inner join B, then define Y = X inner join C, then define Z = Y inner join D, etc. etc. That way when you get to query Z it should be what you need. Just a thought.

 

[Pat Hartman]

It doesn't have an ending semi-colon. Also, if you linked the tables, Access prefixed the table names with the database owner so the names may actually be - dbo_TEAM_MEMBERS and dbo_PROJECT and dbo_ANSWER