Running Sum on a report

[Guest]

I have a report that has a running sum (Over Group) for an element that is
has been told to total on a monthly basis. It is listed on a weekly basis and
totals fine for the monthly total. However, What I want is to take the total
generated on the monthly level and use it as the starting point for the next
month.

Example

Wk ending Pts this wk Pts total
wk 1 3.0 3.0
wk 2 .0 3.0
wk 3 .25 3.25
wk 4 1.0 4.25
Mth totals 4.25 4.25
wk 6 .0 4.25
wk 7 .25 4.50

ideas welcome

 

[Larry Daugherty]

While you can use report tools for some calculations they are intended
for use on *that* report. Like Forms, Reports are presentation
windows. Information that exists only in the presentation doesn't
persist beyond the current session of the Form or Report.

Calculate and manage totals via queries or code. Use the total found
as the beginning value.

HTH

 

[Guest]

Larry,

I understand what you are telling me and I thank you. But how do I do a
query to take the prior records total and add to it for this weeks total?

Thanks,
Kent

 

[Larry Daugherty]

Thee are so many ways you can do it that it's only possible to give
one way and then do some hand waving and leave it up to you to decide
which approach(es) to use. If my response is unhelpful to you then
you might post back and give us a lot more information about what you
already have in play and the level of sophistication you're trying to
hit.

Basic:

Create a Totals query that will total all of the minus entries and
then all of the plus entries over the prior period . To get the
transactions for a target period use "Between [Start date] and [End
date] in the criteria line for the date field. Do the math and you
have the ending/beginning total. You could as easily do that for the
entire time the application has been in play if you don't want to
store balances. Anyway place the result in a control or variable.

Use a similar select query to get the current transactions for your
report. Remember to start the values on the report with the Beginning
Value.

There are any number of variations on a theme. If this is something
done regularly, as it seems it will be, then I'd give some thought to
creating an input form that could receive a single date value as the
end date for the report. and a button to click to launch the report.
All other dates for the queries would be offset dates from that single
Ending Date. Or.... you could enter that single end date on the
launcher and have it fill in other date offset values into other
controls on the form such that you could alter them to suit your
purposes.

HTH

 

[Guest]

Larry,

Thank you for your response. I don't totally understand it but now it is a
moot point as the client changed the request. Thank you again.

Kent

Thee are so many ways you can do it that it's only possible to give
one way and then do some hand waving and leave it up to you to decide
which approach(es) to use. If my response is unhelpful to you then
you might post back and give us a lot more information about what you
already have in play and the level of sophistication you're trying to
hit.

Basic:

Create a Totals query that will total all of the minus entries and
then all of the plus entries over the prior period . To get the
transactions for a target period use "Between [Start date] and [End
date] in the criteria line for the date field. Do the math and you
have the ending/beginning total. You could as easily do that for the
entire time the application has been in play if you don't want to
store balances. Anyway place the result in a control or variable.

Use a similar select query to get the current transactions for your
report. Remember to start the values on the report with the Beginning
Value.

There are any number of variations on a theme. If this is something
done regularly, as it seems it will be, then I'd give some thought to
creating an input form that could receive a single date value as the
end date for the report. and a button to click to launch the report.
All other dates for the queries would be offset dates from that single
Ending Date. Or.... you could enter that single end date on the
launcher and have it fill in other date offset values into other
controls on the form such that you could alter them to suit your
purposes.

HTH
--
-Larry-
--

Larry,

I understand what you are telling me and I thank you. But how do I do a
query to take the prior records total and add to it for this weeks total?

Thanks,
Kent