Running Sum

  • Thread starter Thread starter rebeca
  • Start date Start date
R

rebeca

I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:

Person Header
Person Beginning Accounts Receivable Balance
Date Header
Column labels
Detail
Report data
Date Footer
Daily Totals
Person Footer
Grand Totals for person

The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.

I have the following textboxes in the report
One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.

I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
 
rebeca said:
I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:

Person Header
Person Beginning Accounts Receivable Balance
Date Header
Column labels
Detail
Report data
Date Footer
Daily Totals
Person Footer
Grand Totals for person

The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.

I have the following textboxes in the report
One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.

I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
Click to expand...


For totaling in groups, you need to use running sum Over
Group. Over All works across the entire report.
 
rebeca said:
I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:
Click to expand...
Person Header
    Person                      Beginning Accounts Receivable Balance
Date Header
   Column labels
Detail
   Report data
Date Footer
   Daily Totals
Person Footer
   Grand Totals for person
Click to expand...
The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.
Click to expand...
I have the following textboxes in the report
   One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
   One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
   One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.
Click to expand...
I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
Click to expand...

For totaling in groups, you need to use running sum Over
Group.  Over All works across the entire report.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Marsh. I will try that tomorrow. I'm guessing that I need at
least 2 txt boxes? One for the sum and one to stop at a person change,
is that correct?
 
rebeca said:
rebeca said:
I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:
Click to expand...
Person Header
    Person                      Beginning Accounts Receivable Balance
Date Header
   Column labels
Detail
   Report data
Date Footer
   Daily Totals
Person Footer
   Grand Totals for person
Click to expand...
The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.
Click to expand...
I have the following textboxes in the report
   One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
   One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
   One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.
Click to expand...
I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
Click to expand...

For totaling in groups, you need to use running sum Over
Group.  Over All works across the entire report.
Click to expand...

Thanks Marsh. I will try that tomorrow. I'm guessing that I need at
least 2 txt boxes? One for the sum and one to stop at a person change,
is that correct?
Click to expand...


I don't know what "sum" you are referring to here.
Actually, it's not at all clear why you can not use
=Sum(???) in the group footers for the group totals. Do you
really need to see a running amount on each detail and in
the groups?

If you have to have a running total in the date footers,
then you will need a second running sum text box in the date
footer. Set its expression to =[date footer text box]
with the group total and RunningSum set to Over Group.
 
rebeca said:
rebeca wrote:
I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:
Person Header
    Person                      Beginning Accounts Receivable Balance
Date Header
   Column labels
Detail
   Report data
Date Footer
   Daily Totals
Person Footer
   Grand Totals for person
The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.
I have the following textboxes in the report
   One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
   One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
   One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.
I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
For totaling in groups, you need to use running sum Over
Group.  Over All works across the entire report.
Click to expand...
Click to expand...
Thanks Marsh. I will try that tomorrow. I'm guessing that I need at
least 2 txt boxes? One for the sum and one to stop at a person change,
is that correct?
Click to expand...

I don't know what "sum" you are referring to here.
Actually, it's not at all clear why you can not use
=Sum(???) in the group footers for the group totals.  Do you
really need to see a running amount on each detail and in
the groups?

If you have to have a running total in the date footers,
then you will need a second running sum text box in the date
footer.  Set its expression to =[date footer text box]
with the group total and RunningSum set to Over Group.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Marsh, I finally got back to this. The users decided that they
do not need the daily subtotals, so I removed the header and footer
for batch date. Now each day rolls up into a single line, problem
solved. I was hoping that you would help me with a different yet
similar problem.

In the previously mentioned report I am wondering how to get the
difference between a report start date and the original beginning AR
record and update that starting balance in the report. I'm not
interested in storing the values, just calculating them on the
report. Hope this makes sense.
 
rebeca said:
rebeca wrote:
I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:
Person Header
    Person                      Beginning Accounts Receivable Balance
Date Header
   Column labels
Detail
   Report data
Date Footer
   Daily Totals
Person Footer
   Grand Totals for person
The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.
I have the following textboxes in the report
   One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
   One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
   One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.
I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
For totaling in groups, you need to use running sum Over
Group.  Over All works across the entire report.
Click to expand...
Click to expand...
Thanks Marsh. I will try that tomorrow. I'm guessing that I need at
least 2 txt boxes? One for the sum and one to stop at a person change,
is that correct?
Click to expand...

I don't know what "sum" you are referring to here.
Actually, it's not at all clear why you can not use
=Sum(???) in the group footers for the group totals.  Do you
really need to see a running amount on each detail and in
the groups?

If you have to have a running total in the date footers,
then you will need a second running sum text box in the date
footer.  Set its expression to =[date footer text box]
with the group total and RunningSum set to Over Group.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Marsh, I finally got back to this. The users decided that
they
do not need the daily subtotals, so I removed the header and footer
for batch date. Now each day rolls up into a single line, problem
solved. I was hoping that you would help me with a different yet
similar problem.

In the previously mentioned report I am wondering how to get the
difference between a report start date and the original beginning AR
record and update that starting balance in the report, (meaning if my
beginning AR is January 1 and I run the report on January 15, how to
get the data for January 1-14 and calculate the Beginning AR on
January 20). I'm not
interested in storing the values, just calculating them on the
report. Hope this makes sense.
 
rebeca said:
On Feb 10, 4:52 pm, Marshall Barton  wrote:
rebeca wrote:
I have a report that I am having problems with running sum. It is
basically a daily ledger and is laid out as follows:
Person Header
    Person                      Beginning Accounts Receivable Balance
Date Header
   Column labels
Detail
   Report data
Date Footer
   Daily Totals
Person Footer
   Grand Totals for person
The report works fine as long as I am running it for one person. Page
one calculates the correct runningsum. However, the subsequent pages
tack on the daily total for the previous person. The third page, for
the previous 2 people, etc.
I have the following textboxes in the report
   One textbox labeled "StartSum" in the person header with Running
Sum set to "OverAll"
   One textbox labeled "ARRunningSum" in the detail section with
Running Sum set to "OverAll" Also in this section I have a text box
with =[beginningar]+[arrunningsum] to obtain the line total.
   One textbox labeled "EndSum" in the person footer with Running Sum
set to "OverGroup" Also in this section I have a text box with
=[beginningar]+[arrunningsum] to obtain the daily total.
I have tried these multiple different ways, but cannot get the
information to display correctly after the first page. Any guidance
would be appreciated.
For totaling in groups, you need to use running sum Over
Group.  Over All works across the entire report.
Thanks Marsh. I will try that tomorrow. I'm guessing that I need at
least 2 txt boxes? One for the sum and one to stop at a person change,
is that correct?
Click to expand...
Click to expand...
I don't know what "sum" you are referring to here.
Actually, it's not at all clear why you can not use
=Sum(???) in the group footers for the group totals.  Do you
really need to see a running amount on each detail and in
the groups?
Click to expand...
If you have to have a running total in the date footers,
then you will need a second running sum text box in the date
footer.  Set its expression to =[date footer text box]
with the group total and RunningSum set to Over Group.
Click to expand...
- Show quoted text -
Click to expand...

Thanks Marsh, I finally got back to this.  The users decided that
they
do not need the daily subtotals, so I removed the header and footer
for batch date. Now each day rolls up into a single line, problem
solved. I was hoping that you would help me with a different yet
similar problem.

In the previously mentioned report I am wondering how to get the
difference between a report start date and the original beginning AR
record and update that starting balance in the report, (meaning if my
beginning AR is January 1 and I run the report on January 15, how to
get the data for January 1-14 and calculate the Beginning AR on
January 20). I'm not
interested in storing the values, just calculating them on the
report.  Hope this makes sense.- Hide quoted text -

- Show quoted text -
Click to expand...

Typo, not January 20, January 15. Sorry!
 
That's good. Running totals were ptimatily used to help
cross check the total and some people just have a problem
trusting a total without seeing the running amount.

How are you getting a starting balance now? Whatever you
are doing, it shouldn't be too difficult to modify it.

If you are not getting it now, you can use a text box with a
DSUM expression, but I would need to know exactly where the
beginning AR date and report start date come from.
 
That's good.  Running totals were ptimatily used to help
cross check the total and some people just have a problem
trusting a total without seeing the running amount.




How are you getting a starting balance now?  Whatever you
are doing, it shouldn't be too difficult to modify it.

If you are not getting it now, you can use a text box with a
DSUM expression, but I would need to know exactly where the
beginning AR date and report start date come from.
Click to expand...

Beginning AR date comes from a table that contains provider, ardate,
and beginningar. Report startdate is from a datepicker. forms!
datepicker!startdate. I have a text box on the report referencing the
query for the report, I just added the ARDate and BeginningAR to the
query. In the textbox, I have IIF statements for if the ARDate
matches the start date to populate the beginning AR, if the ARDate is
greater than the report start date to display 0. Can't quite get the
next one to work correctly, not sure if this is right way to go or
not. I'm still learning as I go :)
 
rebeca said:
Beginning AR date comes from a table that contains provider, ardate,
and beginningar. Report startdate is from a datepicker. forms!
datepicker!startdate. I have a text box on the report referencing the
query for the report, I just added the ARDate and BeginningAR to the
query. In the textbox, I have IIF statements for if the ARDate
matches the start date to populate the beginning AR, if the ARDate is
greater than the report start date to display 0. Can't quite get the
next one to work correctly, not sure if this is right way to go or
not. I'm still learning as I go :)
Click to expand...


I think I am lost here so I can't guess if that's the "right
way to go or not".

Maybe something like this shot in the dark is the kind of
thing you want??

IIf(ARDate > [report start date], 0,
DLookup("beginningar", "a table", "provider = " & Provider))
 
Beginning AR date comes from a table that contains provider, ardate,
and beginningar.  Report startdate is from a datepicker. forms!
datepicker!startdate.  I have a text box on the report referencing the
query for the report, I just added the ARDate and BeginningAR to the
query.  In the textbox, I have IIF statements for if the ARDate
matches the start date to populate the beginning AR, if the ARDate is
greater than the report start date to display 0.  Can't quite get the
next one to work correctly, not sure if this is right way to go or
not. I'm still learning as I go :)
Click to expand...

I think I am lost here so I can't guess if that's the "right
way to go or not".

Maybe something like this shot in the dark is the kind of
thing you want??

        IIf(ARDate > [report start date], 0,
DLookup("beginningar", "a table", "provider = " & Provider))

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

I can't imagine that you'd be lost with my awesome descriptions ;).
Let me try and reword.

My reports are generated from selections on a form that contains a
StartDate and EndDate calendar object. It also contains fields to
select the system, report, user, and physician.

What I am wanting to do is to generate the starting balance no matter
what start date the user is selecting and try to meet the following
scenarios.

If ARDATE = Report Start Date, display beginningAR from
SNBalanceHistory
IF ARDATE > Report Start Date, display 0.00 for the dates prior to the
ARDate, calculate the runningsum from ARDate on.
IF ARDATE < Report Start Date, display the BeginningAR + the total of
the previous days back to the ARDate

Still playing with the DLookup...
 
rebeca said:
rebeca said:
In the previously mentioned report I am wondering how to get the
difference between a report start date and the original beginning AR
record and update that starting balance in the report, (meaning if my
beginning AR is January 1 and I run the report on January 15, how to
get the data for January 1-14 and calculate the Beginning AR on
January 15). I'm not
interested in storing the values, just calculating them on the
report.
Click to expand...
How are you getting a starting balance now?  Whatever you
are doing, it shouldn't be too difficult to modify it.
Click to expand...
If you are not getting it now, you can use a text box with a
DSUM expression, but I would need to know exactly where the
beginning AR date and report start date come from.
Click to expand...
Beginning AR date comes from a table that contains provider, ardate,
and beginningar.  Report startdate is from a datepicker. forms!
datepicker!startdate.  I have a text box on the report referencing the
query for the report, I just added the ARDate and BeginningAR to the
query.  In the textbox, I have IIF statements for if the ARDate
matches the start date to populate the beginning AR, if the ARDate is
greater than the report start date to display 0.  Can't quite get the
next one to work correctly, not sure if this is right way to go or
not. I'm still learning as I go :)
Click to expand...

I think I am lost here so I can't guess if that's the "right
way to go or not".

Maybe something like this shot in the dark is the kind of
thing you want??

        IIf(ARDate > [report start date], 0,
DLookup("beginningar", "a table", "provider = " & Provider))
Click to expand...

I can't imagine that you'd be lost with my awesome descriptions ;).
Let me try and reword.

My reports are generated from selections on a form that contains a
StartDate and EndDate calendar object. It also contains fields to
select the system, report, user, and physician.

What I am wanting to do is to generate the starting balance no matter
what start date the user is selecting and try to meet the following
scenarios.

If ARDATE = Report Start Date, display beginningAR from
SNBalanceHistory
IF ARDATE > Report Start Date, display 0.00 for the dates prior to the
ARDate, calculate the runningsum from ARDate on.
IF ARDATE < Report Start Date, display the BeginningAR + the total of
the previous days back to the ARDate

Still playing with the DLookup...
Click to expand...


I think my confusion comes from not knowing which fields are
in which tables and how the records in the ar table relate
to the records in the report's table/query. Pease use the
real names of the tables and their fields.

If the report;s record source is a query, include a
Copy/Paste of the the query's SQL view.

It looks like you condiftions and expressions are long
enough and complicated enough to do this with code in a
header section's Format event instead of a monster text box
expression.
 
Thanks for sticking with me. I'm a fairly new user to Access, but I
pick things up quickly. It sounds like the DLOOKUP may work, I just am
not very familar with that command.

Here is the SQL from the Query.

SELECT [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Physician,
Sum([SN AR Log Query - By Physician].Charges) AS SumOfCharges, Sum([SN
AR Log Query - By Physician].Chg_Adj) AS SumOfChg_Adj, Sum([SN AR Log
Query - By Physician].Payments) AS SumOfPayments, Sum([SN AR Log Query
- By Physician].Pymt_Adj) AS SumOfPymt_Adj, Sum([SN AR Log Query - By
Physician].Other_Adj) AS SumOfOther_Adj, Sum([SN AR Log Query - By
Physician].Refunds) AS SumOfRefunds, Sum([SN AR Log Query - By
Physician].Bad_Debt) AS SumOfBad_Debt, Sum([SN AR Log Query - By
Physician].Total1) AS SumOfTotal1, SNBalanceHistory.ARDate,
SNBalanceHistory.BeginningAR
FROM [SN AR Log Query - By Physician] INNER JOIN SNBalanceHistory ON
[SN AR Log Query - By Physician].MDID = SNBalanceHistory.Physician
GROUP BY [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Physician,
SNBalanceHistory.ARDate, SNBalanceHistory.BeginningAR;

Table Names are:
SNBalanceHistory
MDID - Unique record
Physician - Physician (joined to the SNProvider Table by MDID in
that table)
ARDate - Beginning AR date, multiple entries for each provider as
a new entry is made each month after adjustments, FC, etc.)
BeginningAR

AR Log - Scinet is the main table the data comes from.
Batch ID
Physician
Date Entered in Log
Batch Date
Batch
Charges
Chg_Adj
Payments
Pymt_Adj
Refunds
Other_Adj
Kind_of_Adj
Bad_Debt
Reason for Modification
WindowsLogon
System

Rebeca
 
rebeca said:
Thanks for sticking with me. I'm a fairly new user to Access, but I
pick things up quickly. It sounds like the DLOOKUP may work, I just am
not very familar with that command.

Here is the SQL from the Query.
Click to expand...

I'm modifying the query just to make it easier to read. It
produce exactly the same results.

SELECT ARL.MDID, ARL.[Batch Date], ARL.Physician,
Sum(ARL.Charges) AS SumOfCharges,
Sum{ARL.Chg_Adj) AS SumOfChg_Adj,
Sum(ARL.Payments) AS SumOfPayments,
Sum(ARL.Pymt_Adj) AS SumOfPymt_Adj,
Sum(ARL.Other_Adj) AS SumOfOther_Adj,
Sum(ARL.Refunds) AS SumOfRefunds,
Sum(ARL.Bad_Debt) AS SumOfBad_Debt,
Sum(ARL.Total1) AS SumOfTotal1,
BH.ARDate, BH.BeginningAR
FROM [SN AR Log Query - By Physician] As ARL
INNER JOIN SNBalanceHistory As BH
ON ARL.MDID = BH.Physician
GROUP BY ARL.MDID, ARL.[Batch Date], ARL.Physician,
BH.ARDate, BH.BeginningAR
Table Names are:
SNBalanceHistory
MDID - Unique record
Physician - Physician (joined to the SNProvider Table by MDID in
that table)
ARDate - Beginning AR date, multiple entries for each provider as
a new entry is made each month after adjustments, FC, etc.)
BeginningAR

AR Log - Scinet is the main table the data comes from.
Batch ID
Physician
Date Entered in Log
Batch Date
Batch
Charges
Chg_Adj
Payments
Pymt_Adj
Refunds
Other_Adj
Kind_of_Adj
Bad_Debt
Reason for Modification
WindowsLogon
System
Click to expand...

I appears to me that the beginning ar amount you are trying
to calculate would be a sum from the SNBalanceHistory table
( I don't see where the AR Log - Scinet table would be
needed???

The Domain Aggregate functions (e.g. DLookup, DCount, DSum,
etc) are all easily translated from basic queries that
return a single value. In your case I think(?) you may want
to use DSum something like:

If Me.[report start date] > Me.ARDATE Then
startamt = 0
ElseIf Me.[report start date] = Me.ARDATE Then
startamt = beginningAR
Else 'Me.[report start date] < Me.ARDATE
startamt = beginningAR + _
DSum("BeginningAR", "SNBalanceHistory", _
"ARDate>=" & Format(Me.ARDATE, "\#yyyy-m-d\#") _
& " And ARDate<" & Format(Me.[report start date],
"\#yyyy-m-d\#") _
& " AND Physician=" & Me.Physician)
End If

Most likely, it's not that simple, but it's the best I can
figure. So study that to understand what it does and how it
does it, then maybe you can correct it. Be sure to check
VBA Help for details about the Domain Aggegate functions.
 
Back
Top