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;