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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Bill PrewCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
itsquadSystems AdminAuthor Commented:
Good eye Bill!  Thanks!!
0
Bill PrewCommented:
Welcome.


»bp
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

itsquadSystems AdminAuthor 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
itsquadSystems AdminAuthor Commented:
Fantastic!  Thanks again Bill!
0
itsquadSystems AdminAuthor 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
itsquadSystems AdminAuthor 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
itsquadSystems AdminAuthor 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
itsquadSystems AdminAuthor Commented:
Thanks sooooo much Bill!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.