Cumulative percent totals with DSum

Posted on 2006-04-17
Last Modified: 2010-07-27
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!!

Question by:mbanuet
    LVL 58

    Expert Comment


    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 ;)

    LVL 1

    Author Comment

    Yeah it's a nightmare, I need this in a query :(
    LVL 58

    Expert Comment

    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.

    LVL 1

    Author Comment

    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?
    LVL 18

    Expert Comment

    by:Richard Daneke
    Why do you need this as a query?  What formatting restriction makes a report not work?
    LVL 58

    Accepted Solution


        ... 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.

    LVL 1

    Author Comment

    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!
    LVL 58

    Expert Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now