?
Solved

Do Some Math in a Query

Posted on 2013-02-01
13
Medium Priority
?
254 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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