• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1661
  • Last Modified:

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
0
mbanuet
Asked:
mbanuet
  • 4
  • 3
1 Solution
 
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°)
0
 
mbanuetAuthor Commented:
Yeah it's a nightmare, I need this in a query :(
0
 
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°)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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?
0
 
Richard DanekeCommented:
Why do you need this as a query?  What formatting restriction makes a report not work?
0
 
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°)
0
 
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!
0
 
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°)
0

Featured Post

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now