Accumulative SUM for records in a Report (Leo Asked)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leo - 12 May 2008 08:32 GMT

Dear Expert(s)
I have a Journal Report of my Vouchers based on query
[TotalDebit_Journal-Qry] in which , on any run, the report (underlying query)
asks for start and end of voucher no.
I need to have a textbox at any record of the report showing accumulated sum
from the start of all records exisisting in the table(not only filterred
report) to the current record.
For example , I have 1 to 1000 vouchers in my table, and the user asks for
vouchers 100 to 300 only on that report. So I need a text box to show me the
accumulated sum from voucher 1 to the current record e.g. 1-101 , 1-102,....
I tried it with DSUM, but could not find the result.
please help or give me an idea??!

Signature

Thans & Best regards
Leo, InfoSeeker

Reply to this Message

John Spencer - 12 May 2008 12:19 GMT

How do you select the vouchers 100 to 300 in your query?

How are your vouchers numbered? You say 100 to 300 in one place and then show
1-101, 1-102.

What is the starting "number" for the vouchers? Is the voucher number field a
number field or a text field? If the "numbers" are 1-101 then it has to be a
text field.

Do you want an accumulated total to show for each voucher (a running sum) or
do you just need one sum for the last voucher in the series.

1-101 $20
1-102 $25
1-103 $35

Or just $35 for all vouchers up to 1-103?

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Dear Expert(s)
> I have a Journal Report of my Vouchers based on query
[quoted text clipped - 8 lines]
> I tried it with DSUM, but could not find the result.
> please help or give me an idea??!

Reply to this Message

Leo - 13 May 2008 13:24 GMT

Hi, and thank you for your reply,
let me explain more.
I have a table from Voucher no. 1 to no. 1000 with these records;
VoucherNo.    VoucherAmount
1        20$
2        10$
3        30$
4        25$
.
.
.
1000        40$

Created a report based on a query which asks user to input the starting and
ending vouchers :
for expample if user gets the report from Voucher 3 to 5 ,
it will show;
VoucherNo.    VoucherAmount    AccumulatedSum
3        30        30
4        25        55
5        15        70

I could show a running sum for existing Vouchers in Query/Report,
But What I need is this;
VoucherNo.    VoucherAmount    AccumulatedSum
3        30        60
4        25        85
5        15        100

which shows an accumulated Sum from Voucher 1 to each current voucher,
meaning the accumulated sums are read from Table, not from Query which is
filtered to a range of voucher by user.
Hope this explained the problem.

Signature

Thans & Best regards
Leo, InfoSeeker

> How do you select the vouchers 100 to 300 in your query?
>
[quoted text clipped - 31 lines]
> > I tried it with DSUM, but could not find the result.
> > please help or give me an idea??!

Reply to this Message

John Spencer - 13 May 2008 16:58 GMT

I would expect to see a query that looked like

SELECT VoucherNo, VoucherAmt
, (SELECT Sum(VoucherAmt)
FROM YourTable as Tmp
WHERE Tmp.VoucherNo <= YourTable.VoucherNo) as RunningSum
FROM YourTable
WHERE VoucherNo Beween [Start No] and [End No]

Your other option would be to use the DSUM function as a calculated field
instead of the subquery.

DSUM("VoucherAmt","YourTable","VoucherNo Between 1 and " & [VoucherNo]) as
RunningSum

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Hi, and thank you for your reply,
> let me explain more.
[quoted text clipped - 29 lines]
> filtered to a range of voucher by user.
> Hope this explained the problem.

Reply to this Message

Leo - 14 May 2008 11:07 GMT

thank you so much,
I used your DSUM approach as unbound control on my report this way;
DSUM("VoucherAmt","YourTable","VoucherNo <= Reports!MyReports![VoucherNo]
AND VoucherNo >=1")

Signature

Thans & Best regards
Leo, InfoSeeker

> I would expect to see a query that looked like
>
[quoted text clipped - 49 lines]
> > filtered to a range of voucher by user.
> > Hope this explained the problem.

Reply to this Message

Rate this thread: