difficulty in creating update totals query on same table.

Question

Consider the following table:

ID nonUniqueID value total
--------------------------
1  12345        5     x
2  12345        10    x
3  789          20    x
4  789          5     x

I need to make a query something like this (psuedo SQL), which will work within Access 2007:

UPDATE table 
SET total = SUM(value) 
WHERE nonUniqueID IS SAME;

The result should be as follows:

ID nonUniqueID value total
--------------------------
1  12345        5     15
2  12345        10    15
3  789          20    25
4  789          5     25

I've tried group bys, but I got odd results that quite frankly, I could not interpret. Does anybody know how I could achieve something like this?

Answer 1

Not sure if this works in Access or not, but give it a try:

update table t1
inner join (
    select nonUniqueID, sum(value) as SumValue
    from table
    group by nonUniqueID 
) t2 on t1.nonUniqueID = t2.nonUniqueID
set t1.total = t2.SumValue

Update: Based on this question, it looks like it is not going to work. But give it a shot! If it doesn't, you can use the approach suggested in that question.

Answer 2

Another possible option:

update t 
set total = (select SUM(value) from table where nonUniqueID = t.nonUniqueID)
from table t