DSum - cumulative calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a query to calculate the cumulative scrap percent at each operation for a job. My fields look lik
Job Sequence Scrap% Yield Cumulative Scra
xyz 10 1 0.99
xyz 9 5 0.95
xyz 8 5 0.95
...

Yield is a calculated field. I know the formulas I could use in Excel but am having difficulty converting the logic to Access. I believe that I could use the DSum function. Thanks in advance for any suggestions
 
Hi,




SELECT a.job, a.sequence, a.scrap, a.yield, SUM(b.scrap)
FROM myTable As a INNER JOIN myTable As b
ON a.job=b.job AND a.sequence<=b.sequence
GROUP BY a.job, a.sequence, a.scrap, a.yield




If you try:


SELECT a.job, a.sequence, b.job, b.sequence
FROM myTable As a INNER JOIN myTable As b
ON a.job=b.job AND a.sequence<=b.sequence
ORDER BY a.job, a.sequence, b.sequence

you would observe that SUM(b.scrap), for each a.job, a.sequence produces
indeed the running sum.

If you just want the final sum, it is even easier:


SELECT a.job, MIN(a.sequence), SUM(a.scrap)
FROM myTable As a
GROUP BY a.job





Hoping it may help,
Vanderghast, Access MVP



Birger said:
I'm trying to create a query to calculate the cumulative scrap percent at
Click to expand...
each operation for a job. My fields look like
Job Sequence Scrap% Yield Cumulative Scrap
xyz 10 1 0.99 ?
xyz 9 5 0.95 ?
xyz 8 5 0.95 ?
...

Yield is a calculated field. I know the formulas I could use in Excel but
Click to expand...
am having difficulty converting the logic to Access. I believe that I could
use the DSum function. Thanks in advance for any suggestions.
 
I tried the previous code, it produced an error. Looking at it, I think would calculate the sum of the yield. I need the cumulative percent yield. I have entered the formula calculations in the cumulative yield column. Can anyone help with this problem

Job Sequence Scrap% Yield Cumulative Scra
xyz 10 1 0.99 (1/0.99)*100=1.010
xyz 9 5 0.95 (1/0.99/0.95)*100=1.063
xyz 8 5 0.95 (1/0.99/.95/.95)*100=1.119
....
 
Hi,


SELECT a.Job, a.Sequence, LAST(a.scrap), LAST(a.yield),
1.0 / EXP(SUM(LOG(b.yield))) As CumulativeScrap

FROM myTable As a INNER JOIN myTable As b
ON a.job=b.job AND a.sequence<=b.sequence

GROUP BY a.Job, a.Sequence

ORDER BY a.Job, a.Sequence DESC



Hoping it may help,
Vanderghast, Access MVP




Birger said:
I tried the previous code, it produced an error. Looking at it, I think
Click to expand...
would calculate the sum of the yield. I need the cumulative percent yield.
I have entered the formula calculations in the cumulative yield column. Can
anyone help with this problem?
 
Back
Top