Slow running query

[shawnvon]

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.

[ranman256]

is this a local db? remote sql/oracle?
are there lots of records? 100k? more?
have you compressed the tables?
are there users in the db while you run it?

[shawnvon]

is this a local db? remote sql/oracle?
are there lots of records? 100k? more?
have you compressed the tables?
are there users in the db while you run it?

Thanks for the reply.

The db is on a shared network drive

There are about 50K records in the claims and payment tables each and 80K in tblRates

Running on Access 2016 and have it set to compact and repair and compress

Do you know of any Access add-ins that can correct any of these issues?

[CJ_London]

In addition toRanman's comments - are the relevant fields indexed? - ones you link, sort or filter on?

[ranman256]

i have found access2016 to suck and be really slow.

[ssanfu]

Just an FYI.....

You have columns like

Last(tblES.checkDate) AS LastOfcheckDate

From HELP:

First, Last Functions

Remarks
The First and Last functions are analogous to the MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary


People frequently use Last(), when they really should use Max(), to get the latest date, check number, etc..............