Solved

Calculating Stats within the same Record

Posted on 2011-03-17
4
268 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
  • 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

773 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