Access Update SQL with Sum Error

Question

I am using access as my DB to try to Sum the number of test attempts by subject for each Student for a SchoolGrade. Some basic info: A SchoolGrade has many students, a students test attempts are record by subject. I am wanting to update the total test attempts taken by subject for each SchoolGrade. Below is my query:

UPDATE [SchoolGrade] AS SG
INNER JOIN [Student] AS S ON S.schoolgrade_id = SG.id
SET SG.[Total Reading Test Attempts] = SUM(S.[Reading Test Attempts] ), 
SG.[Total Math Test Attempts] = SUM(S.[Math Test Attempts] ), 
SG.[Total Science Test Attempts] = SUM(S.[ScienceTest Attempts] );

I am getting the following error:

You tried to execute a query that does not include the specified expression 'Total Reading Test Attempts' as part of an aggregate function.

How do I fix this error?

Community · Accepted Answer · 2017-05-23 11:54:57Z

1

To be complete, here's a full set of queries to perform the update and clean up afterward. All due credit to Gordon Linoff's answer and Use SELECT inside an UPDATE query.

Create table:

SELECT s.schoolgrade_id,
             SUM([Reading Test Attempts]) as sum_reading,
             SUM([Math Test Attempts]) as sum_math,
             SUM([Science Test Attempts]) as sum_science
INTO TempTable
FROM [Student] AS s
GROUP BY s.schoolgrade_id

Perform update:

UPDATE SG
SET [Total Reading Test Attempts] = s.sum_reading, 
    [Total Math Test Attempts] = s.sum_math, 
    [Total Science Test Attempts] = s.sum_science
FROM [SchoolGrade] AS SG INNER JOIN
     TempTable AS s
     ON S.schoolgrade_id = SG.id

Clean up:

DROP TABLE TempTable

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered May 12, 2017 at 6:28

C Perkins

3,8854 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-05-12 01:08:33Z

1

You need to aggregate before doing the JOIN. I think this will work in MS Access:

UPDATE SG
    SET [Total Reading Test Attempts] = s.sum_reading, 
        [Total Math Test Attempts] = s.sum_math, 
        [Total Science Test Attempts] = s.sum_science
    FROM [SchoolGrade] AS SG INNER JOIN
         (SELECT s.schoolgrade_id,
                 SUM([Reading Test Attempts]) as sum_reading,
                 SUM([Math Test Attempts]) as sum_math,
                 SUM([Science Test Attempts]) as sum_science
          FROM [Student] AS s
          GROUP BY s.schoolgrade_id
         ) s
         ON S.schoolgrade_id = SG.id

edited May 12, 2017 at 1:08

answered May 11, 2017 at 23:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

user908759 Over a year ago

I get the following error: Syntax error (missing operator) in query expression 's.sum_science FROM [SchoolGrade] AS SG INNER JOIN (SELECT s.schoolgrade_id, SUM([Reading Test Attempts]) as sum_reading, SUM([Math Test Attempts]) as sum_math, SUM([Science Test Attempts]) as sum_science FROM [Student] AS s GROUP BY s.schoolgrade_id ) s ON S.schoolgrade_id = SG.id'.

C Perkins Over a year ago

It is missing the AS for the s alias. Also, Access will not perform updates with JOINS to queries. If you attempt that, it returns the error 'Operation must use an updatable query'. See Use SELECT inside an UPDATE query.

user908759 Over a year ago

C Perkins Creating a table to update from worked! Thank You!

Collectives™ on Stack Overflow

Access Update SQL with Sum Error

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related