Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Calculating Stats within the same Record

Posted on 2011-03-17
Medium Priority
278 Views
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
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
• 2

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 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

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

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

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.

0

## Featured Post

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month4 days, 19 hours left to enroll