Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Calculating Stats within the same Record

Hello Experts,

I have a query that I am building for a complex report.  The headings are:
* Sales Dollars
* Sales Units
* Avg Inv $
* Margin Dollars
* Margin %
* ROI
* Turn Rates

For Sales Dollars, Sales Units, and Avg Inv $, I am just summing the records in the aggregate query.  The Margin Dollars is ([Sales Dollars] - ([Turn Rate] * [Avg Inv $]))/52.

The problem is with calculating Margin % which is: [Margin Dollars] / [Sales Dollars].  When I run that expression, it errors out with an Overflow Error.

What is the best way to calculate data like this?

Thank you!

0
pwdells
Asked:
pwdells
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try using an IIF in the query

IIF([Sales Dollars]=0,0,[Margin Dollars] / [Sales Dollars])

use the format for all calculation that you use, where the divisor could be zero
0
 
LambertHeenanCommented:
Division by zero in a query does not result in a runtime error, it results in JET returning the 'value' #Error.

IF all you are seeing is a dialog box that says 'Overflow' and nothing else other than an OK button then my suspicions point to this part of the calculation

[Turn Rate] * [Avg Inv $]

I suspect that the value being returned is somehow of type Integer and that is why you are getting an overflow error. Try explicitly converting one term of the calculation to currency, which will force the result to be currency: like this...

([Sales Dollars] - ([Turn Rate] * ccur([Avg Inv $])))/52

Or in the part of the query that calculates [Avg Inv $] be sure that that value is forced to be a Currency type.
0
 
pwdellsAuthor Commented:
I am still working on trying to make this work.  I will let you know by the end of today, if it worked.

Thank you!!!
0
 
pwdellsAuthor Commented:
When I created the query again, it resulted in "Over Flow".  I implemented Capricorn's suggestion and it worked.  To be honest, I don't know why I received the other error previous.  

Thank you for your help!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now