# Is it possible to NEST with the DSUM function, like you can do with IIF Statements?

My fields are:
GL      Amount               Type           Dept         Math
R417      \$5.00      Revenue      Band      X
R419      \$6.00      Revenue      Band      X
R425      \$10.00      Revenue      Guitar      X
R426      \$7.00      Revenue      Guitar
R435      \$15.00      Revenue      Drum      X
R645      (\$10.00)      Expense             Guitar

I'd like the DSUM function to return these results:
GL               Amount      Type            Dept         Math      DSUM_Test
R417      \$5.00      Revenue      Band      X      11
R419      \$6.00      Revenue      Band      X      11
R425      \$10.00      Revenue      Guitar      X      10
R426      \$7.00      Revenue      Guitar
R435      \$15.00      Revenue      Drum      X      15
R645      (\$10.00)      Expense              Guitar

Here's my DSUM formula so far...

DSUM_Test: DSum("Amount","TestDSUM","[Dept]='" & [Dept] & "'" And "[Math]='" & [Math] & "'")

My results are:
GL      Amount             Type             Dept   Math      DSUM_Test
R417      \$5.00      Revenue      Band      X      33
R419      \$6.00      Revenue      Band            33
R425      \$10.00      Revenue      Guitar      X      33
R426      \$7.00      Revenue      Guitar            33
R435      \$15.00      Revenue      Drum      X      33
R645      (\$10.00)      Expense              Guitar            33

Is there a different approach I should be exploring on this?  Any help greatly appreciated!  Thanks, Kevin
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You have a couple of extra double quotes in there, it should be:

``````DSUM_Test: DSum("Amount","TestDSUM","[Dept]='" & [Dept] & "' And [Math]='" & [Math] & "'")
``````

»bp
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Good eye Bill!  Thanks!!
0
Commented:
Welcome.

»bp
0
Hi Bill, can you check this one out for me too?  I'm getting a not as expected result when I'm signifying a distinct term for [Type]:

DSum_Rev: DSum("Amount","Department_Q","[Type]='Revenue'" And "[Department]='" & [Department] & "'And [Math_Group]='" & [Math_Group] & "'")

Thanks, Kevin
0
Commented:
Try this:

``````DSum_Rev: DSum("Amount","Department_Q","[Type]='Revenue' And [Department]='" & [Department] & "' And [Math_Group]='" & [Math_Group] & "'")
``````

»bp
0
Fantastic!  Thanks again Bill!
0
Hi Bill, are you aware if it's possible to add two DSUM columns together?

When I tried it, it looks like this:

DSum_Sales      DSum_COG              Sale_COG_Diff: [DSum_Sales]+[DSum_COG]
\$626,713.00      (\$337,171.00)      \$626,713.00(\$337,171.00)

Strange that it just concatenates both values together, instead of performing the math.

I was trying to figure out the Gross Margin percentage on products sold.

Thanks, Kevin
0
Commented:
Try this:

``````Sale_COG_Diff: 0+[DSum_Sales]+[DSum_COG]
``````

»bp
0
Whhhaaat!?!  That's crazy!,............GOOD!  Thanks a lotta!  How did you know to do that?  Thanks again, Kevin

PS - I'd like to handle the margin % all in one column, I'll give it a whirl and let you know :D

PSS - Got it!  It did the division without having to "fake it out" with the 0+ :D
0
Commented:
No wizardry there, in some of these more interpretive languages and SQL it sometimes gets confused what the type of something is (string, number, etc).  Since we were seeing it was doing a string concatenation of the values, it wasn't treating them as numbers.  One trick that's been around for a long time if to trick or coerce the system into treating things as numbers rather than strings.  By adding a number (zero in this case) it sees that and there's no confusion if that is a number or a string. It's a number obviously.  Once it knows one of the terms in the formula is a number, it does the smartest thing it can and treats them all as numbers.

It doesn't always work out that simple, but I tried it in this case and it did what we needed.  Another approach, arguably a more explicit or precise approach, would be to do something like below, where we "CAST" or convert the string data type result to a numeric one.  Hope this helps...

``````Sale_COG_Diff: CDbl([DSum_Sales])+CDbl([DSum_COG])
``````

»bp
0
Right on Bill!!  That's a good new cool one on me!  Thanks for the full info!  :D

Here's my (perhaps fairly sloppy?) formula for getting the Margin all into one column:

Sales_Margin: (Format(Round(DSum("Amount","Department_Q","[Group]='Sales' AND [Type]='Revenue' AND [GL_Accounts.Department]='" & [GL_Accounts.Department] & "'"),0),"Fixed")+0+Format(Round(DSum("Amount","Department_Q","[Group]='Sales' AND [Type]='Expense' AND [GL_Accounts.Department]='" & [GL_Accounts.Department] & "'"),0),"Fixed"))/(Format(Round(DSum("Amount","Department_Q","[Group]='Sales' AND [Type]='Revenue' AND [GL_Accounts.Department]='" & [GL_Accounts.Department] & "'"),0),"Fixed"))

Any critiques welcome!  :D  Thanks again Bill, Kevin
0
Commented:
You might be able to do all the Format and Round just once when the final result is gotten, unless you need to round the sub totals before the final division...

``````Sales_Margin: Format(Round((0+DSum("Amount","Department_Q","[Group]='Sales' AND [Type]='Revenue' AND [GL_Accounts.Department]='" & [GL_Accounts.Department] & "'")+DSum("Amount","Department_Q","[Group]='Sales' AND [Type]='Expense' AND [GL_Accounts.Department]='" & [GL_Accounts.Department] & "'"))/DSum("Amount","Department_Q","[Group]='Sales' AND [Type]='Revenue' AND [GL_Accounts.Department]='" & [GL_Accounts.Department] & "'"),0),"Fixed")
``````

»bp
0