Adding a running total column to a access query

I need to add additional column 'total' to the result set, where it has running total
and select only itemcodes that accounts to total of 95% of cumulative catqty. Any help is greatly appreciated.

Query is as follows:

SELECT v1.category, v1.itemcode, v1.itmqty, v2.catqty, v1.itmqty/v2.catqty*100,
[*Running Total column of v1.itmqty/v2.catqty*100 <--- Code to add this functionality]
FROM [SELECT category,itemcode, sum(qty) as  itmqty
           FROM itemtable
          WHERE nz(qty,0) > 0
          GROUP BY  category,itemcode
          order by 1, 3 desc]. AS v1
INNER JOIN
      [SELECT category, sum(qty) as catqty
       from item_table
       where nz(qty,0) > 0
      group by category]. AS v2
ON v1.category = v2.category
WHERE v1.itmqty/v2.catqty <= 95.00
order by 1,5 desc


Thank You
praveen_kattaAsked:
Who is Participating?
 
GRayLCommented:
Try this, I works for me.

SELECT a.Category, a.ItemCode, Sum(a.OrdQty) AS ItemQty, DSum("OrdQty","myTbl","Category = Category") AS CatQty, Format(ItemQty/CatQty,"Percent") As PctEachItem,
(SELECT Format(Sum(b.OrdQty/DSum("OrdQty","myTbl","Category = Category")),"Percent") FROM myTbl b WHERE b.ItemCode<=a.ItemCode) AS RunSum
FROM myTbl a WHERE (SELECT Sum(b.OrdQty/DSum("OrdQty","myTbl","Category = Category")) FROM myTbl b WHERE b.ItemCode<=a.ItemCode) <=.95
GROUP BY a.Category, a.ItemCode ;
0
 
GRayLCommented:
How many records do you want to see?
0
 
praveen_kattaAuthor Commented:
I need to select all itemcodes upto 95% of the running total column for each group
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
praveen_kattaAuthor Commented:
Here is an example
Data in the table looks something like this:
Category        Itemcode             OrdQty    
A                    A100                   40
A                    A100                     1
A                    A110                   30
A                    A111                   15
A                    A123                   10
A                    A999                     3
A                    Z121                     1

Output should look like this
totalitmqty --> Total of Ordqty of each unique item
catqty ==  Total qty of all the items in each category
%ofeachitemo=  --> % of each item ordered i.e. totalitmqty/catqty*100
Category        Itemcode            totalitmQty     Catqty       %ofeachitem       RunningTotal
A                    A100                   41                  100          41%                    41%
A                    A110                   30                   100         30%                    71%
A                    A111                   15                   100         15%                    86%  
A                    A123                   10                   100          10%                   96%
A                    A999                     3                   100           3%                    99%
A                    Z121                     1                   100           1%                    100%

So in the final output, I want to pickup only items that accounts for <= 95% of the totalqty ordered for each entity. In this case it will be only 3 these rows and the items are A100,A110 & A111. In my example, I have given illustration of only entity A. I will have more than one entity.

A                    A100                   41                  100          41%                    41%
A                    A110                   30                   100         30%                    71%
A                    A111                   15                   100         15%                    86%  
0
 
GRayLCommented:
Do you want queries for the last two recordsets, or just the last one?
0
 
praveen_kattaAuthor Commented:
I want to see all the records, which accounts upto <= 95% of the Rolling Total as mentioned above.
0
 
GRayLCommented:
So that's recordset #3, not #2?
0
 
GRayLCommented:
It works for me.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.