[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Adding a running total column to a access query

Posted on 2009-04-30
Medium Priority
1,919 Views
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
0
Question by:praveen_katta
• 5
• 3

LVL 44

Expert Comment

ID: 24274641
How many records do you want to see?
0

Author Comment

ID: 24274959
I need to select all itemcodes upto 95% of the running total column for each group
0

Author Comment

ID: 24279572
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

LVL 44

Expert Comment

ID: 24280998
Do you want queries for the last two recordsets, or just the last one?
0

Author Comment

ID: 24281776
I want to see all the records, which accounts upto <= 95% of the Rolling Total as mentioned above.
0

LVL 44

Expert Comment

ID: 24283470
So that's recordset #3, not #2?
0

LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 24283677
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

LVL 44

Expert Comment

ID: 24283679
It works for me.

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
###### Suggested Courses
Course of the Month18 days, 8 hours left to enroll