Solved

Do Some Math in a Query

Posted on 2013-02-01
13
252 Views
Last Modified: 2013-02-05
Hi Experts -

I'm trying to do some math in a query or maybe a function; I don’t know for sure but I received a request to (add) several fields together in a format like this:

IS + RS      numerator
IR + ER +RS     denominator

Maybe, this can be done in a crosstab query I’m not sure; any direction or example will be helpful to me. In the end I have to chart this dataset results after calculating. There looking for percentage scraped versus quantity purchased.

See sample data in attachment.

Thanks in Advance,
GeneBat
TestMath.accdb
0
Comment
Question by:GeneBat
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 85
ID: 38844203
You can perform math in a query:

SELECT ID, Field1 + Field2 AS MyCalculatedField FROM SomeTable

SELECT ID, (Col1 + Col2) / Col3 AS MyPct FROM SomeTable

These are known as "calculated" or "derived" fields.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38844780
Two additions to what LSM said,

In the query design view you would enter this calculations (using the examples from LSM) as

MyCalculatedField: Field1+Field2
MyPct: (Col1+Col2) / Col3

Also if any of your values can be NULL in the database you should use NZ otherwise the result of a calculation will be an error or Null

MyPct: (NZ(Col1, 0) + NZ(Col2, 0) / Col3

Also you may want to provide a check in the percentage case so that you don't divide by zero:

MyPct: iif(NZ(Col3)=0, 0, (NZ(Col1, 0) + NZ(Col2, 0) / Col3))
0
 

Author Comment

by:GeneBat
ID: 38845035
I see where you folks are coming from but my data is in a different format. Check photo attached.
FormatofData.PNG
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:kmslogic
ID: 38845133
I guess I don't understand what you're going for.  Maybe you need to group your data by part number like:

SELECT sales_order, SUM(quantity) as TotalQuantity 
FROM TestMath
GROUP BY sales_order

Open in new window


And then do calculations per the above comments on the SUM'd values?
0
 

Author Comment

by:GeneBat
ID: 38845264
Remember this is what I'm trying to accomplish in a query.

IS + RS      numerator
IR + ER +RS     denominator

I have to sum those up.
There in the "notification_type" column.

Thanks
0
 
LVL 85
ID: 38845591
I can't really follow what you want. It looks like the data you've supplied is for a single "sales order" and there are different sorts of "notifications" for that sales order. There are also other columns that contain data, but it's not really clear what that data is or how it's related to your issue.

If you could provide a more clear picture of what you're after, perhaps it would be helpful.
0
 

Author Comment

by:GeneBat
ID: 38851136
Well, I think I have to write this query in two different queries if I can't complete this in one. What I mean is that if you look at my attachment; think I would have to sum these values 1st (IR + ER + RS) then in another query sum (IS + RS) then somehow combine them together in one query to graph the results.

Hopefully this helps.
0
 
LVL 85
ID: 38851363
Could very well be, but it's still not clear what you're trying to do. There are multiple values for each row, so which do you add, and which do you not add?

For example, given the attachment you provided, what should your query ultimately provide? Can you show us that?
0
 

Author Comment

by:GeneBat
ID: 38851503
here's what I have so far. But I need to figure out the percentage scraped.
percent-scrap2.PNG
0
 
LVL 85
ID: 38851561
Can you show the SQL of the query?

How do you calculate the percentage of scrap? Is it scrap/qty?
0
 

Author Comment

by:GeneBat
ID: 38851616
SELECT TestMath.sales_order, TestMath.notification_type, Sum(TestMath.quantity) AS Qty, Sum(TestMath.repair) AS SumOfrepair, Sum(TestMath.scrap) AS SumOfscrap
FROM TestMath
GROUP BY TestMath.sales_order, TestMath.notification_type
HAVING ((Not (TestMath.notification_type)="ER"));

This is the SQL.
I do have sample data here already if you want to run this through it.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38854444
How do you calculate the percentage of scrap? Is it scrap/qty?
Assuming that your scrap percentage is scrap/qty (or something like that), then you'd be best served by creating the query you show above and SAVE That query, and then create a second query based on that saved query. In that second query, you can further perform math to get your percentage.

For example, if you save your query above as "qryScrapData", you could then build a query like this:

SELECT Sales_Order,Qty,SumOfRepair,SumOfScrap, Qty/SumOfScrap AS ScrapPct FROM qryScrapData

Again, I'm not sure how you calculate scrap percentage, so the above would have to be modified for those requirements.
0
 

Author Closing Comment

by:GeneBat
ID: 38855865
The two query method works!
Thanks for the brainstorming.
I with your help; I got it to work.
Again Big Thanks!
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

696 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