Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Do Some Math in a Query

Posted on 2013-02-01
13
Medium Priority
?
263 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

610 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