Solved

Calculating Stats within the same Record

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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