dsum in query

  • Thread starter Thread starter beppe005
  • Start date Start date
B

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
 
Field: TotalHours: DSUM("Hours","YourTable","ID=" & [YourTable].[ID])

That assumes that ID is a number field and not a text field
 
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
Click to expand...
Click to expand...
 
Back
Top