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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
How many records do you want to see?
praveen_kattaAuthor Commented:
I need to select all itemcodes upto 95% of the running total column for each group
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%  
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

GRayLCommented:
Do you want queries for the last two recordsets, or just the last one?
praveen_kattaAuthor Commented:
I want to see all the records, which accounts upto <= 95% of the Rolling Total as mentioned above.
GRayLCommented:
So that's recordset #3, not #2?
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 ;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
It works for me.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.