Cumulative total in a query

[Gary F Shelton]

Sku April FCST May FCST June FCST FCST AVG % of total Cumulative % total
OIF00224 352,872 380,025 390,843 374,580 20% 20%
MCX40 282,570 273,120 291,480 282,390 15% 35%
GSP03953 158,550 198,810 170,100 175,820 9% 45%
MCF03786 120,810 133,230 98,460 117,500 6% 51%
MCF03791 120,000 102,330 101,820 108,050 6% 57%
MCS110 69,525 75,141 74,034 72,900 4% 61%
MCG30 68,130 65,190 63,540 65,620 4% 65%
OIF00910 49,500 71,400 71,640 64,180 3% 68%
MCF03785 67,950 61,710 60,570 63,410 3% 72%
OIF00993 69,150 55,410 48,870 57,810 3% 75%
SPR04605 76,310 73,082 19,365 56,252 3% 78%
SPR04606 65,353 76,569 17,998 53,307 3% 81%
OIF00873 48,492 55,458 52,839 52,263 3% 83%
SYO00234 45,000 50,220 51,210 48,810 3% 86%
MCX03638 56,970 45,600 29,880 44,150 2% 88%
OIF00093 37,740 40,830 42,090 40,220 2% 91%
OIF00276 43,530 31,050 42,210 38,930 2% 93%
SYO00270 35,262 31,725 47,925 38,304 2% 95%
BCI03388 34,020 35,010 38,190 35,740 2% 97%
MCF03783 29,520 27,330 38,220 31,690 2% 98%
OIF00992 30,900 29,070 27,390 29,120 2% 100%

Total 1,851,046


I have wrote queries to get me every column but my last one which is the
cumulative total... Does anyone know how to write a cumulative total query?

 

[Rob Ward]

OK, you have three possibilities.

1) (The easiest) is to output the query so far to a report. In the report
you can switch on a text box property called 'Running Sum' - this accumulates
the numbers as the report runs, and you can use the contents of his text box
in another text bo to calculate your % of total.

2) The second alternative is to use a domain function within the query, but
for this to work you must be able to predict/specify the order in which the
records are going to be displayed, which doesn't seem to be the case in your
example. If the SKU codes were numeric and output in that order, you could
use something like CumTotal: Dsum("FCST AVG", "MyQuery","Sku<=" [Sku]) to
calculate a cumulative column.

If the SKu is alpha (as yours is), then the syntax is quite fiddly:
CumTotal: Dsum("FCST AVG", "MyQuery","Sku<='" & [Sku] & "'")

..