Solved

Calculating Stats within the same Record

Posted on 2011-03-17
4
275 Views
Last Modified: 2012-05-11
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
Comment
Question by:pwdells
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35157967
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
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 35158872
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
 

Author Comment

by:pwdells
ID: 35175446
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
 

Author Comment

by:pwdells
ID: 35177682
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

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question