sumif equivalent in microsoft access

[sheetspread]

If I have a table such as:

[Color] [Number of Colors]
Red 5
Orange 19
Red 9
Green 18
Orange 23

and want to add another column:

[Color] [Number of Colors] [Total Number of This Color]
Red 5 14
Orange 19 42
Red 9 14
Green 18 18
Orange 23 42

In excel, the sumif command does this easily, but in Access, what is the best way to add a conditional sum field that uses each record as a reference?

 

[Norie]

Use a totals query.

Group by Color and Sum Number of Colors.

The SQL would look something like this.

SELECT ColorTable.Color, Sum(ColorTable.[Number of Colors]) AS [SumOfNumber of Colors]
FROM ColorTable
GROUP BY ColorTable.Color;

If you want the exact layout you've posted use DSum.

DSum("[ColorTable]![Number of Colors]","[ColorTable]","[ColorTable]![Color]='" & [Color] & "'")

 

[Philem]

Norie was faster.

-P