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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|