help with DSUM (1 Viewer)

p4nny

New member
Local time
Today, 01:32
Joined
Mar 16, 2020
Messages
15
Hello

I'm trying sum a field with multiple criteria

Here's what I have tried

=dsum("[2020]","resources","[Role ID]"=text124 and "[ref no]"<>9999)

It is to sum column "2020" in the "resources" table WHERE "Role ID" equals the value in "text124" AND "ref no" does not equal "9999".

Much appreciated
 

June7

AWF VIP
Local time
Yesterday, 17:32
Joined
Mar 9, 2014
Messages
5,423
Concatenation is not correct.

=DSum("[2020]", "resources", "[Role ID]=" & [text124] & " AND [ref no]<>9999")
 

plog

Banishment Pending
Local time
Yesterday, 20:32
Joined
May 11, 2011
Messages
11,613
[2020] should not be a column in a database table. You don't store values in table or field names. Instead resources needs a year field (e.g. [ResourceYear]) and the value 2020 should go into it.

How many columns named after years does this table have?
 

onur_can

Active member
Local time
Yesterday, 18:32
Joined
Oct 4, 2015
Messages
180
plog is a correct naming that causes 2020 domain name mess. instead you should use a name that clearly describes your domain.

Example:
Code:
=DSum("ProductYear", "resources", "[Role ID]=" & [text124] & " AND [ref no]<>9999")
 

plog

Banishment Pending
Local time
Yesterday, 20:32
Joined
May 11, 2011
Messages
11,613
My point was very clearly stated and was not about domain name issues. It was that his table structure is not normalized. Further, in a properly structured system you would not sum the [ProductYear] field.
 

Users who are viewing this thread

Top Bottom