[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
8
Medium Priority
?
1,919 Views
Last Modified: 2012-05-06
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
Comment
Question by:praveen_katta
  • 5
  • 3
8 Comments
 
LVL 44

Expert Comment

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

Author Comment

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

Author Comment

by:praveen_katta
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 44

Expert Comment

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

Author Comment

by:praveen_katta
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

by:GRayL
ID: 24283470
So that's recordset #3, not #2?
0
 
LVL 44

Accepted Solution

by:
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

by:GRayL
ID: 24283679
It works for me.

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question