A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report Help - Starting/Ending Balance



 
 
Thread Tools Display Modes
  #1  
Old August 18th, 2009, 08:07 PM posted to microsoft.public.access.reports
rebeca
external usenet poster
 
Posts: 43
Default Report Help - Starting/Ending Balance

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)
  #2  
Old August 18th, 2009, 10:01 PM posted to microsoft.public.access.reports
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Report Help - Starting/Ending Balance

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.


"rebeca" wrote:

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)

  #3  
Old August 18th, 2009, 10:24 PM posted to microsoft.public.access.reports
rebeca
external usenet poster
 
Posts: 43
Default Report Help - Starting/Ending Balance

On Aug 18, 4:01*pm, KARL DEWEY
wrote:
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.



"rebeca" wrote:
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)- Hide quoted text -


- Show quoted text -


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;
  #4  
Old August 20th, 2009, 05:49 PM posted to microsoft.public.access.reports
rebeca
external usenet poster
 
Posts: 43
Default Report Help - Starting/Ending Balance

On Aug 18, 4:01*pm, KARL DEWEY
wrote:
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.



"rebeca" wrote:
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)- Hide quoted text -


- Show quoted text -


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]);
  #5  
Old August 20th, 2009, 06:08 PM posted to microsoft.public.access.reports
John... Visio MVP
external usenet poster
 
Posts: 900
Default Report Help - Starting/Ending Balance

"KARL DEWEY" wrote in message
...
I do not know what an 'AR balance' is ...


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

John... Visio MVP

  #6  
Old August 20th, 2009, 09:38 PM posted to microsoft.public.access.reports
rebeca
external usenet poster
 
Posts: 43
Default Report Help - Starting/Ending Balance

On Aug 20, 12:08*pm, "John... Visio MVP"
wrote:
"KARL DEWEY" wrote in message

...

I do not know what an 'AR balance' is ...


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

John... Visio MVP


bump.
  #7  
Old August 21st, 2009, 04:01 PM posted to microsoft.public.access.reports
rebeca
external usenet poster
 
Posts: 43
Default Report Help - Starting/Ending Balance

On Aug 20, 11:49*am, rebeca wrote:
On Aug 18, 4:01*pm, KARL DEWEY
wrote:





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.


"rebeca" wrote:
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)- Hide quoted text -


- Show quoted text -


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 -


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!
  #8  
Old August 21st, 2009, 04:25 PM posted to microsoft.public.access.reports
rebeca
external usenet poster
 
Posts: 43
Default Report Help - Starting/Ending Balance

On Aug 20, 11:49*am, rebeca wrote:
On Aug 18, 4:01*pm, KARL DEWEY
wrote:





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.


"rebeca" wrote:
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)- Hide quoted text -


- Show quoted text -


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 -


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!


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.