When I run this query, I get the System Resources Exceeded Error:
Code:
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:
Code:
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.