DSUM HELP!!!

[GD]

I'm trying to use a query to subtotal the amounts in Field2, based on the
common values in Field1. What is the exact expression I need to perform this
function?

(What I have in Table 1) (What I need from the query)
Field1 Field2 NewField
1 $1.10 $6.60
1 $2.20 $6.60
1 $3.30 $6.60
2 $4.40 $9.90
2 $5.50 $9.90

 

[Jeff Boyce]

What have you tried so far?

Have you checked the DSum() function syntax in Access HELP?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GD]

DSum("[Table1]![Field2]","[Table1]","[Table1]![Field1]")

but it returns the total for all amounts in Field2, not based on Field1.
How do I phrase the criteria portion?

 

[John Spencer]

Using DSum

Field: MyTotal: DSum("Field2","Table1","Field1=" & Table1.Field1)

Alternative is to use a sub-query

Field: MyTotal: (SELECT SUM(Temp.Field2) FROM Table1 as Temp WHERE
Temp.Field1 = Table1.Field1)

Or even more efficient would be to use a subquery in the FROM clause of
your query and join on Field1.

SELECT Table1.Field1
, Table1.Field2
, Temp.TheSum
FROM Table1 INNER JOIN
(SELECT Field1, Sum(Field2) as TheSum
FROM Table1
GROUP BY Field1) as Temp
ON Table1.Field1 = Temp.Field1

You can only do the subquery in the FROM clause if your field and table
names following the naming convention (ONLY Letters, Numbers, and the
underscore for names).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[GD]

The sub-query worked like a champ!! Thanks, John!
--
GD

Using DSum

Field: MyTotal: DSum("Field2","Table1","Field1=" & Table1.Field1)

Alternative is to use a sub-query

Field: MyTotal: (SELECT SUM(Temp.Field2) FROM Table1 as Temp WHERE
Temp.Field1 = Table1.Field1)

Or even more efficient would be to use a subquery in the FROM clause of
your query and join on Field1.

SELECT Table1.Field1
, Table1.Field2
, Temp.TheSum
FROM Table1 INNER JOIN
(SELECT Field1, Sum(Field2) as TheSum
FROM Table1
GROUP BY Field1) as Temp
ON Table1.Field1 = Temp.Field1

You can only do the subquery in the FROM clause if your field and table
names following the naming convention (ONLY Letters, Numbers, and the
underscore for names).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

DSum("[Table1]![Field2]","[Table1]","[Table1]![Field1]")

but it returns the total for all amounts in Field2, not based on Field1.
How do I phrase the criteria portion?