Problem with MS Access and calculating Average of a Column

I have this MS Access query:

SELECT DISTINCT 
    Career.careerId, Student.studentName, Avg([Student-Topic].grade), Career.careerName
FROM 
    Career INNER JOIN 
        (
        (Student INNER JOIN [Student-Topic] 
            ON Student.studentId = [Student-Topic].studentId) 
                INNER JOIN [Student-Career] 
                    ON Student.studentId = [Student-Career].studentId) 
        ON Career.careerId = [Student-Career].careerId
WHER开发者_C百科E 
    (((
        [Student-Career].careerId)=[Career].[careerId]) AND 
        (([Student-Topic].studentId)=[Student].[studentId]));

Without the Avg function the query works fine, but when I place it, it breaks down...

What's my error?

Does this query express the averages you're looking for?

SELECT studentId, Avg(grade) AS average_grade
FROM [Student-Topic]
GROUP BY studentId;

If so, you could save it as a separate query and JOIN it with your original query. Or include it within the original query as a subquery.

Edit: Oops. A subquery could be problematic since the table name must be bracketed ... that can confuse Access' query designer when it uses square brackets around a subquery. Better to give the table a name which doesn't require bracketing ... Student_Topic instead of Student-Topic.

you must group your data when using aggregate functions.

• i.e. add a GROUP BY clause.