You did not do as I suggested - to create a separate query to find the last
report date and join it in your query.
You also have a lot of contradictory requirements in your query.
-- Sum([SN AR Log Query - By Physician].total1) + [SN AR Log Query - By
Physician].amount AS RunningSum,
This is adding all records for the period for total1 to every single record
for amount. It can not be done this way.
You then want all individual records for total1 to be displayed. You can not
roll up records and see individual records at the same time within the same
query unless you are using DSum function.
You further mixed my suggested separate query here --
WHERE ((([SN AR Log Query - By Physician].[Batch
Date])<[FORMS]![DATEPICKER]![STARTDATE]))
The separate query was to find last date before the start of the current
report.
Why do you join RunningSumBatchInfo.[Batch ID] as you do not use any data
from that source?
--
Build a little, test a little.
:
On Aug 21, 4:51 pm, KARL DEWEY <
[email protected]>
wrote:
I looked over your previous post and did not see table structure so this will
be open ended.
You will need a field that uniquely identifies the records - Key.
Create a select query like this --
SELECT Key, Max([YourDateField]) AS LastEntry
FROM Your Table
WHERE [YourDateField] < [ReportStartDate]
GROUP BY Key;
Then add the above query in your report select query joining on the Key
field.
It will return the last record before the start of your report.
--
Build a little, test a little.
:
I'm looking for assistance with the below query. I need the query to
look for the previous entry for "RunningSum". For example, if Iam
running a report for August 1, it would need to look for last last
record entered in July. It may be on the 31st, it may not.
=IIf([Beginning_AR_Date]>Forms!datepicker!startdate,"$0.00",IIf
([Beginning_AR_Date] Between Forms!datepicker!startdate And Forms!
datepicker!enddate,[amount],IIf([Beginning_AR_Date]<Forms!datepicker!
enddate,[RunningSum],"$0.00")))
Any help is appreciated.- Hide quoted text -
- Show quoted text -
Thank you for your help. I am still struggling with this. I have the
query built as you advised and it works perfectly. However, now when I
run my report, the field in the report header where my IIF statement
is, returns with "No records found".
Here is the SQL for the query:
SELECT [SN AR Log Query - By Physician].MDID, Max([SN AR Log Query -
By Physician].[Batch Date]) AS LastEntry, [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 RunningSumBatchInfo INNER JOIN ([SN AR Log Query - By Physician]
INNER JOIN [SNProvider Table] ON [SN AR Log Query - By Physician].MDID
= [SNProvider Table].MDID) ON RunningSumBatchInfo.[Batch ID] = [SN AR
Log Query - By Physician].[Batch ID]
WHERE ((([SN AR Log Query - By Physician].[Batch Date])<[FORMS]!
[DATEPICKER]![STARTDATE]))
GROUP BY [SN AR Log Query - By Physician].MDID, [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, [SNAR
Log Query - By Physician].Bad_Debt, [SN AR Log Query - By
Physician].Total1, [SN AR Log Query - By Physician].[Batch Date]
ORDER BY [SN AR Log Query - By Physician].[Batch Date];
What am I doing wrong?- Hide quoted text -
- Show quoted text -
