Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Do Some Math in a Query

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
GeneBat
Asked:
GeneBat
  • 6
  • 5
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
kmslogicCommented:
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
 
GeneBatAuthor Commented:
I see where you folks are coming from but my data is in a different format. Check photo attached.
FormatofData.PNG
0
Industry Leaders: 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!

 
kmslogicCommented:
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
 
GeneBatAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GeneBatAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GeneBatAuthor Commented:
here's what I have so far. But I need to figure out the percentage scraped.
percent-scrap2.PNG
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the SQL of the query?

How do you calculate the percentage of scrap? Is it scrap/qty?
0
 
GeneBatAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GeneBatAuthor Commented:
The two query method works!
Thanks for the brainstorming.
I with your help; I got it to work.
Again Big Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now