Cumulative percent totals with DSum

I need help with cumulative totals:

I have a query that summarizes the following columns


Year      ArtID      Description      Qty            Perc
2004      68030015      Item1      252,991.76      7.31%
2004      69020001      Item2      203,835.60      5.89%
2004      70060172      Item3      99,306.96      2.87%
2004      69230006      Item4      90,326.69      2.61%
2004      68030005      Item5      81,470.08      2.36%
2004      68030010      Item6      76,686.18      2.22%

2005      68030015      Item1      617,907.43      13.96%
2005      68030005      Item5      202,453.31      4.57%
2005      69020001      Item2      169,085.21      3.82%
2005      70060172      Item3      136,532.41      3.08%
2005      70060042      Item7      86,801.18      1.96%
2005      69080120      Item8      81,293.51      1.84%
2005      70050009      Item9      79,486.80      1.80%

The percent column is the percent that corresponds to the year's Grand total.

I need to add an: "Accumulated Percentage" column that computes the accumulated for each year.


Year      ArtID      Description      Qty            Perc      Ac Perc
2004      68030015      Item1      $252,991.76      7.31%      7.31%
2004      69020001      Item2      $203,835.60      5.89%      13.21%
2004      70060172      Item3      $99,306.96      2.87%      16.08%
2004      69230006      Item4      $90,326.69      2.61%      18.69%
2004      68030005      Item5      $81,470.08      2.36%      21.05%
2004      68030010      Item6      $76,686.18      2.22%      23.26%

2005      68030015      Item1      617,907.43      13.96%      13.96%
2005      68030005      Item5      202,453.31      4.57%      18.53%
2005      69020001      Item2      169,085.21      3.82%      22.35%
2005      70060172      Item3      136,532.41      3.08%      25.44%
2005      70060042      Item7      86,801.18      1.96%      27.40%
2005      69080120      Item8      81,293.51      1.84%      29.24%
2005      70050009      Item9      79,486.80      1.80%      31.03%

I've been trying to use DSum but with no good results. Other times the query becomes "frozen".

How can I compute this? I've been having a bad time with this!!

Please help!!

Magda
LVL 1
mbanuetAsked:
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.

harfangCommented:
Hello,

I hope you do not need this in a query, but rather in a report. In a report, simply make a second copy of the control showing the percent, and change the property Running Sum: Over Group.

That's it. Now if you really need this in a query, we can work on it, but it is a nightmare ;)

Cheers!
(°v°)
mbanuetAuthor Commented:
Yeah it's a nightmare, I need this in a query :(
harfangCommented:
For example, a cumulative percentage based on ArtID as secondary sort order would be something like:

SELECT qry.*, (
    Select Sum(Perc)
    From qry As TMP
    Where [Year] = qry.[Year]
      And ArtID <= qry.ArtID
    ) As PercCumul
FROM qry

qry being your current query. But you will probably want to build that into your existing query, in which case you would have to publish it here. However, this solution can slow down the query enormously, depending on indexes and field types.

Cheers!
(°v°)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbanuetAuthor Commented:
I'm testing your solution. As predicted, is very slow...

Also I forgot to mention that the thing is, I need the cumulative total based on the Sum of qty orderered descending, so the part that says: ArtID <= qry.ArtID has no use to my purpose.

Another idea?
Richard DanekeTrainerCommented:
Why do you need this as a query?  What formatting restriction makes a report not work?
harfangCommented:
Then:

    ... And ( Qty < qry.Qty Or Qty = qryQty And ArtID <= qry.ArtID )

You could pull it off with just Qty <= qry.Qty, but you can have duplicates, so the above uses ArtID to resolve those.

The problem with those queries is that they are quadratic. For 100 records, each row will filter and sum up 100 records. With 1000 records, you will be 100 times slower. We are used to this sort of things from Excel, but Excel uses static cells and very long dependencies chains, which makes this much faster to compute.

Basically, you don't do that in a database system. Cumulative sums are for very final outputs, hence reports, excel sheets and charts, etc. When this is needed in large financial databases, you would create a temporary table (a make table query from your first sample), and then use code to loop through the records and fill the new cumulative field. This is never done dynamically with subqueries like the one above.

If you want to go that way, I can show the basic VB structure for such a loop on a temp table.

Cheers!
(°v°)

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
mbanuetAuthor Commented:
Sorry that I didn't followed up before. Actually I had it working the same week you posted but couldn't find the time to post.

I worked around this with your solution but first I converted the results from the query into a table. Then I added a condition to filter records and the result came a lot faster.

thanks a lot!!!

This will be used to obtain reports with a web tool. That's why I couldn't use VB code or else.

My best regards!
harfangCommented:
Glad it works. Depending on the web tool, you could also consider calculating the cumulative percentage while building the page, but I guess you know that.
Good luck!
(°v°)
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.