G
George
I have a query that seems to run either fast (30 seconds) or not at all. When it hangs, Access gives a "Not Responding" error in Task Manager. I usually have to abort the query and retry. The query involves a sort on 4 tables, three of which are linked from an external database. Two tables are almost 1,000,000 records in size.
For the curious, here's the SQL:
SELECT Region.Region, [Core Group].Description AS [Core Group], Sum(Loss.IBNRBalance) AS Bal1, Sum([Loss boy].IBNRBalance) AS Bal2, [Bal1]-[Bal2] AS YTD
FROM Region INNER JOIN ((Loss INNER JOIN [Core Group] ON Loss.CoreGroup = [Core Group].CoreGroup) INNER JOIN [Loss boy] ON (Loss.CompanyNo = [Loss boy].CompanyNo) AND (Loss.AccidentYear = [Loss boy].AccidentYear) AND (Loss.CoreGroup = [Loss boy].CoreGroup) AND (Loss.StateAbbr = [Loss boy].StateAbbr) AND ([Core Group].CoreGroup = [Loss boy].CoreGroup)) ON (Region.StateAbbr = Loss.StateAbbr) AND (Region.StateAbbr = [Loss boy].StateAbbr)
GROUP BY Region.Region, [Core Group].Description;
Why does the query work sometimes and fail sometimes?
TIA
For the curious, here's the SQL:
SELECT Region.Region, [Core Group].Description AS [Core Group], Sum(Loss.IBNRBalance) AS Bal1, Sum([Loss boy].IBNRBalance) AS Bal2, [Bal1]-[Bal2] AS YTD
FROM Region INNER JOIN ((Loss INNER JOIN [Core Group] ON Loss.CoreGroup = [Core Group].CoreGroup) INNER JOIN [Loss boy] ON (Loss.CompanyNo = [Loss boy].CompanyNo) AND (Loss.AccidentYear = [Loss boy].AccidentYear) AND (Loss.CoreGroup = [Loss boy].CoreGroup) AND (Loss.StateAbbr = [Loss boy].StateAbbr) AND ([Core Group].CoreGroup = [Loss boy].CoreGroup)) ON (Region.StateAbbr = Loss.StateAbbr) AND (Region.StateAbbr = [Loss boy].StateAbbr)
GROUP BY Region.Region, [Core Group].Description;
Why does the query work sometimes and fail sometimes?
TIA