Please, help me to sum numbers by ID in one table and update another table with result in SUM field. Something like that:
First_table:
| ID | Not relevant data | Number | ||
|---|---|---|---|---|
| 1 | Not relevant data | Number1 | ||
| 2 | Not relevant data | Number2 | ||
| 1 | Not relevant data | Number3 | ||
| 2 | Not relevant data | Number4 |
Please, help me to sum numbers by ID in one table and update another table with result in SUM field. Something like that:
First_table:
| ID | Not relevant data | Number | ||
|---|---|---|---|---|
| 1 | Not relevant data | Number1 | ||
| 2 | Not relevant data | Number2 | ||
| 1 | Not relevant data | Number3 | ||
| 2 | Not relevant data | Number4 |
Second_table:
| ID | Not relevant data | SUM - need to calculate and update with First_Table | ||
|---|---|---|---|---|
| 1 | Not relevant data | Number1+Number3 | ||
| 2 | Not relevant data | Number2+Number4 |
First table will be bigger with many different IDs, second table is grouping and connecting data via SQL script. I need to sum numbers from First_table into SUM field in Second_Table by their IDs respectively.
Tried to connect Group By query result with table field, but "operation must use an updateable query" error popped up. I'm total newbie in Access and don't know how to solve this issue. Please, help!
Share Improve this question asked Feb 6 at 13:37 Sad_EngineerSad_Engineer 31 silver badge1 bronze badge New contributor Sad_Engineer is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct. 2- This might get you started stackoverflow.com/questions/57187767/… – Bart McEndree Commented Feb 6 at 13:42
- This might also help stackoverflow.com/questions/19789709/… – Bart McEndree Commented Feb 6 at 13:49
1 Answer
Reset to default 1ccess blocks all update queries when grouped queries are included. Use DSUM instead.
UPDATE Second_Table SET SumField=DSUM("NumberField_First_Table","First_Table","ID_in_First_Table=" & [ID_inSecond_Table]);