Running Sum in query problem

  • Thread starter Thread starter DevourU
  • Start date Start date
D

DevourU

Lemme know if I am going at this the wrong way!
I have a field [extcost] that I want a running sum created from. The query
is sorted by [extcost] descending. (A report will do this, but I need
running sum info in a query.) I need to find which items make up the top 80%
of inventory value.

part123 $9 $9
part120 $8 $17
part676 $6 $23
part343 $5 $28
part118 $0 $28

Total $28 * .8 = $22.4 -All parts over $22.4 running sum makeup the top 80%.
(I call this Class A)

I have been at this for two days, and I'd buy anyone a case of beer if I
could! Help :(

-JS
 
I guess you can do something like (a lazy way):

SELECT Main.PartNo, Main.ExtCost,
DSum("ExtCost", "YourTable", "[ExtCost] > = " & Main.ExtCost) As
ProgressiveTotal
FROM YourTable As Main
ORDER BY ExtCost Desc

I think there will be inconsistencies in the ProgressiveTotal if you have 2
or more parts with the same ExtCost. If you want to avoid this, the sorting
of the Records and the criteria for the Dsum needs to refined further.
 
DevourU said:
Lemme know if I am going at this the wrong way!
I have a field [extcost] that I want a running sum created from. The query
is sorted by [extcost] descending. (A report will do this, but I need
running sum info in a query.) I need to find which items make up the top 80%
of inventory value.

part123 $9 $9
part120 $8 $17
part676 $6 $23
part343 $5 $28
part118 $0 $28

Total $28 * .8 = $22.4 -All parts over $22.4 running sum makeup the top 80%.
(I call this Class A)

I have been at this for two days, and I'd buy anyone a case of beer if I
could! Help :(

-JS
Click to expand...

If I'm understanding this correctly, all part numbers are distinct.
So you start with

SELECT PartNum, (Total * .8) As MaxAmount
FROM MyTable
GROUP BY PartNum
HAVING (Total * .8) >= 22.4

Hope this helps. I code for beer.
 
I like your angle, but I am having a problem applying it. If I
sum[extcost]*.8 to get MaxAmount, how am I getting my top 80% of inventory
parts? Maybe if you explain yuor process, I may understand what your doing?
Thankx a bunch.

-Brain Dead
SELECT PartNum, (Total * .8) As MaxAmount
FROM MyTable
GROUP BY PartNum
HAVING (Total * .8) >= 22.4
Click to expand...



Adam Turner via AccessMonster.com said:
DevourU said:
Lemme know if I am going at this the wrong way!
I have a field [extcost] that I want a running sum created from. The query
is sorted by [extcost] descending. (A report will do this, but I need
running sum info in a query.) I need to find which items make up the top
80%
of inventory value.

part123 $9 $9
part120 $8 $17
part676 $6 $23
part343 $5 $28
part118 $0 $28

Total $28 * .8 = $22.4 -All parts over $22.4 running sum makeup the top
80%.
(I call this Class A)

I have been at this for two days, and I'd buy anyone a case of beer if I
could! Help :(

-JS
Click to expand...

If I'm understanding this correctly, all part numbers are distinct.
So you start with

SELECT PartNum, (Total * .8) As MaxAmount
FROM MyTable
GROUP BY PartNum
HAVING (Total * .8) >= 22.4

Hope this helps. I code for beer.
Click to expand...
 
I get #ERROR missing operator '[extcost] > = 6162.8'
EXP1: DSum("ExtCost","ABC Cycle","[ExtCost] > = " & [runningsum] & "")
Thankx for the help.

-Brain Dead

Van T. Dinh said:
I guess you can do something like (a lazy way):

SELECT Main.PartNo, Main.ExtCost,
DSum("ExtCost", "YourTable", "[ExtCost] > = " & Main.ExtCost) As
ProgressiveTotal
FROM YourTable As Main
ORDER BY ExtCost Desc

I think there will be inconsistencies in the ProgressiveTotal if you have
2 or more parts with the same ExtCost. If you want to avoid this, the
sorting of the Records and the criteria for the Dsum needs to refined
further.

--
HTH
Van T. Dinh
MVP (Access)



DevourU said:
Lemme know if I am going at this the wrong way!
I have a field [extcost] that I want a running sum created from. The
query is sorted by [extcost] descending. (A report will do this, but I
need running sum info in a query.) I need to find which items make up the
top 80% of inventory value.

part123 $9 $9
part120 $8 $17
part676 $6 $23
part343 $5 $28
part118 $0 $28

Total $28 * .8 = $22.4 -All parts over $22.4 running sum makeup the top
80%. (I call this Class A)

I have been at this for two days, and I'd buy anyone a case of beer if I
could! Help :(

-JS
Click to expand...
Click to expand...
 
I freakin' got it! Damn, I'm happy, for now.
RunTot: DSum("ExtCost","ABC Cycle","[ExtCost]<=" & [runningsum] & "")

-JS

DevourU said:
I get #ERROR missing operator '[extcost] > = 6162.8'
EXP1: DSum("ExtCost","ABC Cycle","[ExtCost] > = " & [runningsum] & "")
Thankx for the help.

-Brain Dead

Van T. Dinh said:
I guess you can do something like (a lazy way):

SELECT Main.PartNo, Main.ExtCost,
DSum("ExtCost", "YourTable", "[ExtCost] > = " & Main.ExtCost) As
ProgressiveTotal
FROM YourTable As Main
ORDER BY ExtCost Desc

I think there will be inconsistencies in the ProgressiveTotal if you have
2 or more parts with the same ExtCost. If you want to avoid this, the
sorting of the Records and the criteria for the Dsum needs to refined
further.

--
HTH
Van T. Dinh
MVP (Access)



DevourU said:
Lemme know if I am going at this the wrong way!
I have a field [extcost] that I want a running sum created from. The
query is sorted by [extcost] descending. (A report will do this, but I
need running sum info in a query.) I need to find which items make up
the top 80% of inventory value.

part123 $9 $9
part120 $8 $17
part676 $6 $23
part343 $5 $28
part118 $0 $28

Total $28 * .8 = $22.4 -All parts over $22.4 running sum makeup the top
80%. (I call this Class A)

I have been at this for two days, and I'd buy anyone a case of beer if I
could! Help :(

-JS
Click to expand...
Click to expand...
Click to expand...
 
I am having problems with duplicate cost values. Any thoughts?

-JS

Van T. Dinh said:
I guess you can do something like (a lazy way):

SELECT Main.PartNo, Main.ExtCost,
DSum("ExtCost", "YourTable", "[ExtCost] > = " & Main.ExtCost) As
ProgressiveTotal
FROM YourTable As Main
ORDER BY ExtCost Desc

I think there will be inconsistencies in the ProgressiveTotal if you have
2 or more parts with the same ExtCost. If you want to avoid this, the
sorting of the Records and the criteria for the Dsum needs to refined
further.

--
HTH
Van T. Dinh
MVP (Access)



DevourU said:
Lemme know if I am going at this the wrong way!
I have a field [extcost] that I want a running sum created from. The
query is sorted by [extcost] descending. (A report will do this, but I
need running sum info in a query.) I need to find which items make up the
top 80% of inventory value.

part123 $9 $9
part120 $8 $17
part676 $6 $23
part343 $5 $28
part118 $0 $28

Total $28 * .8 = $22.4 -All parts over $22.4 running sum makeup the top
80%. (I call this Class A)

I have been at this for two days, and I'd buy anyone a case of beer if I
could! Help :(

-JS
Click to expand...
Click to expand...
 
I did mention this in my first reply.

Basically, you need some other additional sorting criteria so that the ORDER
BY and the DSUM can be refined further to distibgush between 2 or more items
of the same ExtCost.
 
Back
Top