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
itsquadAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Bill PrewConnect With a Mentor Commented:
You have a couple of extra double quotes in there, it should be:

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

Open in new window


»bp
0
 
itsquadAuthor Commented:
Good eye Bill!  Thanks!!
0
 
Bill PrewCommented:
Welcome.


»bp
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
itsquadAuthor Commented:
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
 
Bill PrewCommented:
Try this:

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

Open in new window


»bp
0
 
itsquadAuthor Commented:
Fantastic!  Thanks again Bill!
0
 
itsquadAuthor Commented:
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
 
Bill PrewCommented:
Try this:

Sale_COG_Diff: 0+[DSum_Sales]+[DSum_COG]

Open in new window


»bp
0
 
itsquadAuthor Commented:
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
 
Bill PrewCommented:
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])

Open in new window


»bp
0
 
itsquadAuthor Commented:
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
 
Bill PrewCommented:
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")

Open in new window


»bp
0
 
itsquadAuthor Commented:
Thanks sooooo much Bill!
0
All Courses

From novice to tech pro — start learning today.