By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

dsum in a query

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"beppe005" <be**************@virgilio.it> wrote:
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


You don't need DSum.

Include [ID] and [Hours] on a query grid. Click the 'totals' button (like
an upper case 'E') on the toolbar. Under [ID] select 'Group by' from the
drop-down and for [Hours] select 'Sum'.

Regards,
Keith.
www.keithwilby.org.uk
Nov 13 '05 #2

P: n/a
"beppe005" <be**************@virgilio.it> wrote in
news:9u**********************@news3.tin.it:
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


A better way to set up something like this is to use a Summation
query joined back to the table in a second query.

If you must use the dsum() you would create a calculated field
containing dsum("hours","tablename"."[tablename].[id] = " & [id])

The veresion if ID is text and not a number is
dsum("hours","tablename"."[tablename].[id] = '" & [id] & "'")
That way, Access knows to sum on the ID field in the table, using
the ID from the query

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #3

P: n/a
Thanks!

"beppe005" <be**************@virgilio.it> ha scritto nel messaggio
news:9u**********************@news3.tin.it...
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

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.