1

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?

Juan Mellado
  • 14,973
  • 5
  • 47
  • 54
rageingnonsense
  • 93
  • 1
  • 2
  • 7
  • Due to Access's limitations, I found the easiest solution was to do a select statement, and then looping through the result set in VBA to run the updates. – rageingnonsense Aug 10 '10 at 23:34

2 Answers2

3

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.

Community
  • 1
  • 1
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
2

Another possible option:

update t 
set total = (select SUM(value) from table where nonUniqueID = t.nonUniqueID)
from table t
Fosco
  • 38,138
  • 7
  • 87
  • 101