dsum in query

[beppe005]

I would like to calculate a total for all the [hours] field with the same
[ID] in a query.
I don't know how to set the "criteria" for this dsum function, it should be
something like [ID]=[ID] but it doesn't make sense.

The query is something like:

+--[id] --- [client] ---- [personel] ----[hours] --- [What I'd like to
get]---+
+--1-----Alpha-------a1----------------1------------4------------------+
+--1-----Alpha-------a2----------------3------------4------------------+
+--2-----Beta--------b1----------------1------------7------------------+
+--2-----Beta--------b2----------------6------------7------------------+
and so on

TIA
Beppe

 

[John Spencer (MVP)]

Field: TotalHours: DSUM("Hours","YourTable","ID=" & [YourTable].[ID])

That assumes that ID is a number field and not a text field

 

[Steve Schapel]

Beppe,

Another approach that may be worth considering, is to make and save a
Totals Query (let's say you call it ClientHours), the SQL view of which
would look something like this...
SELECT [id], Sum([hours]) AS HoursPerID
FROM YourTable
GROUP BY [id]
.... and then join this back to your table and use the totals directly.
The SQL of the second query would look something like...
SELECT [YourTable].[id], [client], [personnel], [hours], [HoursPerID]
FROM YourTable INNER JOIN ClientHours ON YourTable.[id]=ClientHours.[id]

--
Steve Schapel, Microsoft Access MVP

Field: TotalHours: DSUM("Hours","YourTable","ID=" & [YourTable].[ID])

That assumes that ID is a number field and not a text field

I would like to calculate a total for all the [hours] field with the same
[ID] in a query.
I don't know how to set the "criteria" for this dsum function, it should be
something like [ID]=[ID] but it doesn't make sense.

The query is something like:

+--[id] --- [client] ---- [personel] ----[hours] --- [What I'd like to
get]---+
+--1-----Alpha-------a1----------------1------------4------------------+
+--1-----Alpha-------a2----------------3------------4------------------+
+--2-----Beta--------b1----------------1------------7------------------+
+--2-----Beta--------b2----------------6------------7------------------+
and so on

TIA
Beppe