Re: DSUM HELP!!!

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 wrote:
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?

.

Relevant Pages