Report Help - Starting/Ending Balance

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

rebeca

I have a query calculates a running AR balance for multiple
physicians. Here is what I need to accomplish on a report.

Each physician has a record in the provider table that reflects their
initial Beginning AR balance and the date of the AR Balance. For
example, one physician has a Beginning AR date of 07/01/09. This
information is included in the query.

1.) How can I get the balance to display only for 07/01/09-07/31/09?
Any date/amount prior to the Beginning Date should be empty.
2.) How do I then take the Ending AR Balance on 07/31/09 and use that
as the Beginning Balance as of 08/01/09, etc. without writing it to a
table (if possible)
 
I do not know what an 'AR balance' is nor do I know what fields and datatype
your table has. If I had this kind on information I might be able to suggest
a possible solution. Of course sample data would also be helpful.
I could use the SQL of a query you have tried in working toward the results.
If you open the query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post I could see it.
 
I do not know what an 'AR balance' is nor do I know what fields and datatype
your table has.  If I had this kind on information I might be able to suggest
a possible solution.  Of course sample data would also be helpful.
I could use the SQL of a query you have tried in working toward the results.
 If you open the query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post I could see it.
--
Build a little, test a little.







- Show quoted text -
Click to expand...

Sorry fairly new to posting questions so I'm not familar with what is
helpful or not.
Rephrasing my question from before.

[Amount] needs to be displayed for the initial month as the beginning
balance. Anything prior to the [Beginning_AR_Date] should be blank.
Then, [RunningSum] from the last date of the month needs to be
displayed as the Balance for each subsequent month.

The report itself has the beginning balance in the header. Then every
entry that has been made is listed and grouped/totaled by date. In the
footer of the report is the totals of all the entries and the ending
balance.

Does this help at all?

SELECT [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SNProvider Table].Beginning_AR_Date,
[SN AR Log Query - By Physician].Amount, Sum([SN AR Log Query - By
Physician].total1)+[SN AR Log Query - By Physician].amount AS
RunningSum, [SN AR Log Query - By Physician].Charges, [SN AR Log Query
- By Physician].Chg_Adj, [SN AR Log Query - By Physician].Payments,
[SN AR Log Query - By Physician].Pymt_Adj, [SN AR Log Query - By
Physician].Other_Adj, [SN AR Log Query - By Physician].Refunds, [SN AR
Log Query - By Physician].Bad_Debt, [SN AR Log Query - By
Physician].Total1
FROM [SN AR Log Query - By Physician] INNER JOIN [SNProvider Table] ON
[SN AR Log Query - By Physician].MDID = [SNProvider Table].MDID
GROUP BY [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SNProvider Table].Beginning_AR_Date,
[SN AR Log Query - By Physician].Amount, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1;
 
I do not know what an 'AR balance' is nor do I know what fields and datatype
your table has.  If I had this kind on information I might be able to suggest
a possible solution.  Of course sample data would also be helpful.
I could use the SQL of a query you have tried in working toward the results.
 If you open the query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post I could see it.
--
Build a little, test a little.







- Show quoted text -
Click to expand...

going to try to explain this a different way.

When the report is opened, I need to compare the selected start date
for the report to the date associated to the beginning AR
(beginning_AR_date). If the dates are the same month and year, I want
this amount to display in the [Amount] field. If they are not the
same, I need the runningsum for the start date of the report
displayed.

Fields:
([forms]![datepicker]![start date])
[beginning_ar_date]
[amount]
[runningsum]

Here is my SQL information:

SELECT [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SN AR Log Query - By Physician].Amount,
Sum([SN AR Log Query - By Physician].total1)+[SN AR Log Query - By
Physician].amount AS RunningSum, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]<Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date])
AS ReportDate

FROM [SN AR Log Query - By Physician] INNER JOIN [SNProvider Table] ON
[SN AR Log Query - By Physician].MDID = [SNProvider Table].MDID

GROUP BY [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SNProvider Table].Beginning_AR_Date,
[SN AR Log Query - By Physician].Amount, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]<Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date]);
 
KARL DEWEY said:
I do not know what an 'AR balance' is ...
Click to expand...

Accounts Receivable - What people owe you as opposed to Accounts
Payable - What you owe others.

John... Visio MVP
 
I do not know what an 'AR balance' is nor do I know what fields and datatype
your table has.  If I had this kind on information I might be able tosuggest
a possible solution.  Of course sample data would also be helpful.
I could use the SQL of a query you have tried in working toward the results.
 If you open the query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post I could see it.
Click to expand...
- Show quoted text -
Click to expand...

going to try to explain this a different way.

When the report is opened, I need to compare the selected start date
for the report to the date associated to the beginning AR
(beginning_AR_date). If the dates are the same month and year, I want
this amount to display in the [Amount] field. If they are not the
same, I need the runningsum for the start date of the report
displayed.

Fields:
([forms]![datepicker]![start date])
[beginning_ar_date]
[amount]
[runningsum]

Here is my SQL information:

SELECT [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SN AR Log Query - By Physician].Amount,
Sum([SN AR Log Query - By Physician].total1)+[SN AR Log Query - By
Physician].amount AS RunningSum, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]<Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date])
AS ReportDate

FROM [SN AR Log Query - By Physician] INNER JOIN [SNProvider Table] ON
[SN AR Log Query - By Physician].MDID = [SNProvider Table].MDID

GROUP BY [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SNProvider Table].Beginning_AR_Date,
[SN AR Log Query - By Physician].Amount, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]<Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date]);- Hide quoted text -

- Show quoted text -
Click to expand...

Update. Here is part of the resolution to my issue. I wrote an IIF
statement and attached it to the unbound text box that displays the
beginning balance.

=IIf([Beginning_AR_Date] Between Forms!datepicker!startdate And Forms!
datepicker!enddate,[amount],IIf([Beginning_AR_Date]>Forms!datepicker!
startdate,"0.00",IIf([Beginning_AR_Date]<Forms!datepicker!enddate,
[RunningSum],"error")))

My only question now is for the last IIF statement. How do I code it
to show the running balance for the last date of the previous month? I
ran the report for August. The "beginning balance" is showing the
balance on August 3 which was the first date that entries were made
for August.

Thanks!
 
I do not know what an 'AR balance' is nor do I know what fields and datatype
your table has.  If I had this kind on information I might be able tosuggest
a possible solution.  Of course sample data would also be helpful.
I could use the SQL of a query you have tried in working toward the results.
 If you open the query in design view, click on VIEW - SQL View, highlight
all, copy, and paste in a post I could see it.
Click to expand...
- Show quoted text -
Click to expand...

going to try to explain this a different way.

When the report is opened, I need to compare the selected start date
for the report to the date associated to the beginning AR
(beginning_AR_date). If the dates are the same month and year, I want
this amount to display in the [Amount] field. If they are not the
same, I need the runningsum for the start date of the report
displayed.

Fields:
([forms]![datepicker]![start date])
[beginning_ar_date]
[amount]
[runningsum]

Here is my SQL information:

SELECT [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SN AR Log Query - By Physician].Amount,
Sum([SN AR Log Query - By Physician].total1)+[SN AR Log Query - By
Physician].amount AS RunningSum, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]<Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date])
AS ReportDate

FROM [SN AR Log Query - By Physician] INNER JOIN [SNProvider Table] ON
[SN AR Log Query - By Physician].MDID = [SNProvider Table].MDID

GROUP BY [SN AR Log Query - By Physician].MDID, [SN AR Log Query - By
Physician].[Batch Date], [SN AR Log Query - By Physician].Batch,
[SNProvider Table].Physician, [SNProvider Table].Beginning_AR_Date,
[SN AR Log Query - By Physician].Amount, [SN AR Log Query - By
Physician].Charges, [SN AR Log Query - By Physician].Chg_Adj, [SN AR
Log Query - By Physician].Payments, [SN AR Log Query - By
Physician].Pymt_Adj, [SN AR Log Query - By Physician].Other_Adj, [SN
AR Log Query - By Physician].Refunds, [SN AR Log Query - By
Physician].Bad_Debt, [SN AR Log Query - By Physician].Total1, IIf
([Batch Date]<Forms!DatePicker![Start Date],#1/1/1000#,[Batch Date]);- Hide quoted text -

- Show quoted text -
Click to expand...

Update. Here is part of the resolution to my issue. I wrote an IIF
statement and attached it to the unbound text box that displays the
beginning balance.

=IIf([Beginning_AR_Date] Between Forms!datepicker!startdate And
Forms!
datepicker!enddate,[amount],IIf([Beginning_AR_Date]>Forms!datepicker!
startdate,"0.00",IIf([Beginning_AR_Date]<Forms!datepicker!enddate,
[RunningSum],"error")))

My only question now is for the last IIF statement. How do I code it
to show the running balance for the last date of the previous month?
I
ran the report for August. The "beginning balance" is showing the
balance on August 3 which was the first date that entries were made
for August.


Thanks!
 
Back
Top