Hi, is there any better way to run this so it doesn't take ~45-1min?
SELECT
tblISR.ID,
tblISR.Source,
tblISR.Entity,
tblISR.BillTypeMne,
tblISR.SubmittedTo,
Last(tblES.Payor) AS LastOfPayor,
tblPayorsLegend.planNormalname,
tblISR.EncounterID,
tblISR.CertificateNumber,
tblISR.Last,
tblISR.First,
tblISR.Dob,
tblES.AccountNumber,
tblISR.DateofService,
tblISR.ProcCode,
tblISR.ProcedureDescr,
Last(tblISR.DxOneCode) AS LastOfDxOneCode,
Last(tblISR.DxTwoCode) AS LastOfDxTwoCode,
Last(tblISR.DxThreeCode) AS LastOfDxThreeCode,
Last(tblISR.DxFourCode) AS LastOfDxFourCode,
tblISR.ProviderName,
tblISR.Qty,
tblISR.AmtBilled,
tblISR.DateBilled,
tblISR.AppAmt,
tblISR.AmtPaid,
tblISR.DatePaid,
Sum([rate]*IIf(IsNull([Qty])=True,1,[Qty])) AS contractRate,
Sum(tblES.PaidAmount) AS SumOfPaidAmount,
[contractRate]-[SumofPaidAmount] AS variance,
Last(tblES.CheckNo) AS LastOfCheckNo,
Last(tblES.checkDate) AS LastOfcheckDate, tblISR.Comments
FROM tblRates INNER JOIN
((tblISR INNER JOIN tblES ON (tblISR.[AmtBilled] = tblES.[TotalCharges]) AND
(tblISR.[PatientAccount] = tblES.AccountNumber) AND
(tblISR.[ProcCode] = tblES.[CPTCode]))
INNER JOIN tblPayorsLegend ON tblISR.[HealthPlanNm] = tblPayorsLegend.planName) ON (tblRates.cpt = tblES.[CPTCode]) AND
(tblRates.planAbbrev = tblPayorsLegend.planAbbrev)
WHERE (((tblISR.DateofService) Between [effectiveDate] And [endDate]))
GROUP BY
tblISR.ID,
tblISR.Source,
tblISR.Entity,
tblISR.BillTypeMne,
tblISR.SubmittedTo,
tblPayorsLegend.planNormalname,
tblISR.EncounterID,
tblISR.CertificateNumber,
tblISR.Last,
tblISR.First,
tblISR.Dob,
tblES.AccountNumber,
tblISR.DateofService,
tblISR.ProcCode,
tblISR.ProcedureDescr,
tblISR.ProviderName,
tblISR.Qty,
tblISR.AmtBilled,
tblISR.DateBilled,
tblISR.AppAmt,
tblISR.AmtPaid,
tblISR.DatePaid,
tblISR.Comments,
tblES.CPTCode
ORDER BY tblISR.Last, tblISR.DateofService, tblISR.ProcCode;
__________________________________________________ __
Where tblISR is claims and tblES are payments for those claims
If mre information is needed please let me know, this is my first post in Acces forums.
I appreciate the help in advance.