Solved

Do Some Math in a Query

Posted on 2013-02-01
13
200 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
I see where you folks are coming from but my data is in a different format. Check photo attached.
FormatofData.PNG
0
 
LVL 16

Expert Comment

by:kmslogic
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:GeneBat
Comment Utility
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 84
Comment Utility
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
Comment Utility
here's what I have so far. But I need to figure out the percentage scraped.
percent-scrap2.PNG
0
 
LVL 84
Comment Utility
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
Comment Utility
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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
Comment Utility
The two query method works!
Thanks for the brainstorming.
I with your help; I got it to work.
Again Big Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now