Running Sum

[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.

 

[Marshall Barton]

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.

 

[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.

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 -

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?

 

[Marshall Barton]

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?

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]

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?

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 -

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]

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?

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 -

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]

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?

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.

- Show quoted text -

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 -

Typo, not January 20, January 15. Sorry!

 

[Marshall Barton]

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.

 

[rebeca]

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.

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

 

[Marshall Barton]

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

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))

 

[rebeca]

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

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 -

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

 

[Marshall Barton]

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.

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.

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

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))

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

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.

 

[rebeca]

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

 

[Marshall Barton]

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.

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

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.