469,575 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

How to Calculate Cumulative Percent Failures?

294 100+
Hi all,

I have a problem that I have been looking at for a couple days now and I can not quite get it to work.

I would like to calculate the cumulative percent failure of a certain product in my table.

:: Cumulative Percent Failure Example:
If you had 100 product shipped, and every month 10 units failed, you would have 10% failure the first month, 20% the second, and so on.

The fields in my table that I am concerned with are [Ship Year], which tells me what year the model shipped, [Model Number] which tells me which product line I am looking at, [QUANTITY] which tells me how many of the units from the product line there are (units produced), [M2F] which tells what month I am on (months to fail), and [SumOfDETAIL_QTY] which tells me how many units failed for that month.

Key factors:

  1. There is more than one product line in the table (1-30 possible)
  2. I need to only consider cumulative failures among alike product lines
I will include what I have done so far:

Here is the SQL code I am using to obtain how many of the products failed in a given month, depending on what year it shipped.

Expand|Select|Wrap|Line Numbers
  1.  SELECT Pre_Analysis.[Ship Year], Pre_Analysis.[Model Number], Pre_Analysis.M2F, Sum(Pre_Analysis.DETAIL_QTY) AS SumOfDETAIL_QTY, Shipping.Quantity
  2. FROM Shipping INNER JOIN Pre_Analysis ON (Shipping.[Ship Year] = Pre_Analysis.[Ship Year]) AND (Shipping.[Model Number] = Pre_Analysis.[Model Number])
  3. GROUP BY Pre_Analysis.[Ship Year], Pre_Analysis.[Model Number], Pre_Analysis.M2F, Shipping.Quantity;
Here is a picture of what the query produces as of now

Any help on this matter would be greatly appreciated.
Jun 5 '07 #1
3 6504
12,516 Expert Mod 8TB
Is this going into a report? Just use a running sum on the report.
Jun 5 '07 #2
294 100+
Is this going into a report? Just use a running sum on the report.
It would need to be in a table if possible, if not I will not worry about it.
Jun 6 '07 #3
12,516 Expert Mod 8TB
What about multiple subqueries? One to get the total sum and one to get the sum up to that date.
Jun 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Yannick Turgeon | last post: by
4 posts views Thread by Matt Larkin | last post: by
4 posts views Thread by Bill | last post: by
2 posts views Thread by bartsimpson8882002 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.