Accumulative SUM for records in a Report (Leo Asked)

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

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

--
Thans & Best regards
Leo, InfoSeeker


John Spencer said:
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
[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??!
Click to expand...
Click to expand...
 
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
 
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")
--
Thans & Best regards
Leo, InfoSeeker


John Spencer said:
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.
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.
Click to expand...
Click to expand...
 
Back
Top