Have the Passthru Query below that is not "pulling" the correct value for
"LatestPayorPymtDate."

How should this query be tweaked to obtain the correct value for "LatestPayorPymtDate" and also values for the following:

AmtLatestPayorPymt
DateFirstPayorPymt
AmtFirstPayorPymt

SELECT distinct AL1.ACCOUNT_ID as AcctOrig, AL1.ACCOUNT_ID as AcctCurrent, AL7.ENCOUNTER_NO as EncNo, AL2.CUSTOMER_TYPE as CustType, AL6.LAST_NAME as LastName, AL6.FIRST_NAME as FirstName, AL6.RECORDS_NO as RN, AL2.ADMIT_DATE as AdmitDate, AL2.DISCHARGE_DATE as DischDate, AL2.DATE_BILLED as DateBilled, AL2.TOTAL_PAYMENTS as TotPymts, AL2.TOTAL_CHARGES as TotChgOrig, AL2.TOTAL_CHARGES as TotChgCurrent, AL2.EXPECTED_PAYMENT as ExpReimbOrig, AL2.EXPECTED_PAYMENT as ExpReimbCurrent, AL1.TOTAL_PAYMENTS as TotInsPymtsOrig, AL1.TOTAL_PAYMENTS as TotInsPymtsCurrent, AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS as BalAfterInsPymts, AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS as BalAfterAllPymts, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as OrigRatio , AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT as RatioLatest, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS as OthPymts, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES) as CoveredCharges, AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT) as AllowOrig, AL2.TOTAL_CHARGES - Sum ( AL5.ADJUSTMENT_AMOUNT) as AllowCurrent, AL7.DATE_INTERFACED as DateIdentified, AL2.LENGTH_OF_STAY as LOS, AL2.DRG_NO as DrgNo, MAX ( AL7.PAYMENT_DATE ) as LatestPayorPymtDate, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceCurrent, AL2.EXPECTED_PAYMENT - (AL2.TOTAL_CHARGES - Sum (AL5.ADJUSTMENT_AMOUNT)) as VarianceOrig

FROM CV_OCXVW.ACCOUNT_PAYOR AL1, CV_OCXVW.CUSTOMER_ENCOUNTER AL2, CV_OCXVW.ACCOUNT_TRANSACTION_DETAILS AL5, CV_OCXVW.CUSTOMER AL6, CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL7 WHERE ( AL2.ENCOUNTER_NO = AL1.ENCOUNTER_NO AND AL2.ENCOUNTER_NO=AL5.ENCOUNTER_NO AND AL6.CUSTOMER_NO=AL2.CUSTOMER_NO AND AL7.ENCOUNTER_NO=AL1.ENCOUNTER_NO) AND (AL1.ACCOUNT_ID Not In ('D15','D16','D17'))
AND AL2.EXPECTED_PAYMENT>0 AND AL5.TRANSACTION_CODE in ('86004','86020','86035','86036','86037') AND AL1.RANK=1 AND AL7.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012',' 47013') AND AL7.PAYMENT_DATE IN (SELECT AL14.PAYMENT_DATE FROM CV_OCXVW.ACCOUNT_PAYMENT_DETAIL AL14 WHERE (AL14.TRANSACTION_CODE in ('47003','47008','47009','47010','47011','47012', '47013'))) AND trunc(AL7.DATE_INTERFACED) = trunc (sysdate) - 1

GROUP BY AL1.ACCOUNT_ID, AL1.ACCOUNT_ID, AL7.ENCOUNTER_NO, AL2.CustType, AL6.LAST_NAME, AL6.FIRST_NAME, AL6.RECORDS_NO, AL2.ADMIT_DATE, AL2.DISCHARGE_DATE, AL2.DATE_BILLED, AL2.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES, AL2.TOTAL_CHARGES, AL2.EXPECTED_PAYMENT, AL2.EXPECTED_PAYMENT, AL1.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS, AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS, AL2.EXPECTED_PAYMENT - AL2.TOTAL_PAYMENTS, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT, AL1.TOTAL_PAYMENTS / AL2.EXPECTED_PAYMENT, AL2.TOTAL_PAYMENTS - AL1.TOTAL_PAYMENTS, AL2.TOTAL_CHARGES - (AL2.NONCOVERED_DC_CHARGES + AL2.NONCOVERED_CT_CHARGES), AL7.DATE_INTERFACED,AL7.DATE_UPDATED, AL2.LENGTH_OF_STAY, AL2.DRG_NO HAVING AL2.EXPECTED_PAYMENT - AL1.TOTAL_PAYMENTS >=5000
ORDER BY 26

Note, the local Access table contains several calculated values that I need to update on a daily basis. Also, I need to append several 100 records to the local Access table daily as well. Hence, I will use the following steps to update and append records:

Daily Update Process
Step 1. Perform Passthru Query
Step 2. Insert results into Microsoft Access temporary table
Step 3. Inner Join on EncounterNo
Step 4. Update select fields on a subset of records in local Access table
Step 5. Populate "DateUpdated" field on local Access table with today's date
Step 6. Clear out temporary table

Daily Append Process
Step 1. Perform Passthru Query
Step 2. Insert results into local Access table
Step 3. Populate "DateCreated" field with today's date.

Thanks in advance.