System Resources Exceeded

[dashiellx]

When I run this query, I get the System Resources Exceeded Error:

SELECT tluBillingEntity.BillingEntityCode, tblARDetailMaster.PatientName, [tblARDetailMaster].[EncounterType] & '-' & [tluMedicalService].[Mnemonic] AS [Enct/MedServ], tblARDetailMaster.FinancialEncounterNumber, tluFinancialClass.FinancialClassShortName, tblARDetailMaster.AdmissionDate, tblARDetailMaster.DischargeDate, tblARDetailMaster.TotalEncounterBalance, tblARDetailMaster.Insurance1Plan, 1 AS COB, tblARDetailMaster.Insurance1MemberNbr, tblARDetailMaster.AttendingPhysicianName, varARDPolicy([Insurance1MemberNbr]) AS RegError
FROM ((tblARDetailMaster INNER JOIN tluFinancialClass ON tblARDetailMaster.PrimaryFinancialClass = tluFinancialClass.FinancialClass) INNER JOIN tluBillingEntity ON tblARDetailMaster.BillingEntity = tluBillingEntity.BillingEntity) INNER JOIN tluMedicalService ON tblARDetailMaster.MedicalService = tluMedicalService.MedicalService
WHERE (((tblARDetailMaster.AdmissionDate)>=#3/13/2020#) AND ((tblARDetailMaster.Insurance1Plan)="Amerigroup / Americaid MCO"));

However, I do not get this error when running this query:

SELECT tluBillingEntity.BillingEntityCode, tblARDetailMaster.PatientName, [tblARDetailMaster].[EncounterType] & '-' & [tluMedicalService].[Mnemonic] AS [Enct/MedServ], tblARDetailMaster.FinancialEncounterNumber, tluFinancialClass.FinancialClassShortName, tblARDetailMaster.AdmissionDate, tblARDetailMaster.DischargeDate, tblARDetailMaster.TotalEncounterBalance, tblARDetailMaster.Insurance2Plan, 2 AS COB, tblARDetailMaster.Insurance2MemberNbr, tblARDetailMaster.AttendingPhysicianName, varARDPolicy([Insurance1MemberNbr]) AS RegError
FROM ((tblARDetailMaster INNER JOIN tluFinancialClass ON tblARDetailMaster.PrimaryFinancialClass = tluFinancialClass.FinancialClass) INNER JOIN tluBillingEntity ON tblARDetailMaster.BillingEntity = tluBillingEntity.BillingEntity) INNER JOIN tluMedicalService ON tblARDetailMaster.MedicalService = tluMedicalService.MedicalService
WHERE (((tblARDetailMaster.AdmissionDate)>=#3/13/2020#) AND ((tblARDetailMaster.Insurance2Plan)="Amerigroup / Americaid MCO"));

The only difference between the two, is the first query is looking at tblARDetailMaster.Insurance1Plan and the second query is looking at tblARDetailMaster.Insurance2Plan. Both fields in the table have the same set up. (I know this table structure isn't the best, unfortunately, I cannot update the table structure. )


If I remove the "Amerigroup / Americaid MCO" from the first query, it runs perfectly. Changing the affintiy of Access in the Task Manager to one processors also allows the query to run.

[Gicu]

Could be some bad data in the first field, can you try to create a new query in which you apply the criteria on the admissiondate and Insurance1Plan fields and see if that runs.

Cheers,

[dashiellx]

This runs perfectly:

SELECT tblARDetailMaster.Insurance1Plan, tblARDetailMaster.AdmissionDateFROM tblARDetailMaster
WHERE (((tblARDetailMaster.Insurance1Plan)="Amerigroup / Americaid MCO") AND ((tblARDetailMaster.AdmissionDate)>=#3/13/2020#));

[dashiellx]

For S&G's I removed the index from the field in the table and the first query now runs perfectly.