CODEWITHSUNDEEP
sqlms-accessms-access-2007sql-updateaggregate
rageingnonsense
rageingnonsense

Reputation: 93

Difficulty in creating update totals query on same table

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?

Upvotes: 1

Views: 1782

Answers (2)

Fosco
Fosco

Reputation: 38526

Another possible option:

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

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171451

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.

Upvotes: 3

Related Questions