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?

Gordon Linoff · Answer 1 · 2017-05-12T01:08:33.023

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 '17 at 01:08

answered May 11 '17 at 23:07

Gordon Linoff

1,242,037
58
646
786

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'. – user908759 May 12 '17 at 01:02
1

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](http://stackoverflow.com/questions/871905/use-select-inside-an-update-query). – C Perkins May 12 '17 at 04:40
C Perkins Creating a table to update from worked! Thank You! – user908759 May 12 '17 at 05:22

score 1 · Accepted Answer · edited May 23 '17 at 11:54

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

Access Update SQL with Sum Error

2 Answers2