Access SQL - Bit of inefficient SQL help

[kickstart]

Hi

 

I have a system running with an Access back end (unfortunately not my choice). There is a request I need to run and I have come up with some SQL that does do the job but runs like a dog.

 

Simply put the system has a table containing tests results for items. An item can have several tests and each test can occur many times (over months). What I need to get back is list of all the items / tests where the last 3 results for that item / test have been failures.

 

The SQL I have (which does work) is:-

 

SELECT COUNT(*) FROM( SELECT ItemId,TestId, FailCount FROM( SELECT ItemId,TestId, COUNT(*) AS FRED
from TestResults a
where ResultId IN (SELECT Top 3 ResultID from TestResults b Where a.ItemId = b.ItemId AND a.TestID = b.TestId ORDER BY TestDate DESC )
AND TestFail = true)
group by ItemId, TestId)
WHERE FailCount >= 3

 

This has an inner select select getting the last 3 results id for an item / test, counts the number of items / tests where they are fails and where the result id is found in the inner select, and then has an outer count to get a total off all the results where the count is 3 or more (in reality none will ever be more than 3, as only 3 are returned in the inner select).

 

Any suggestions as to how to make this run acceptably efficiently?

 

All the best

 

Keith

[GeoffOs]

If this was SQL Server something like:

select count(ItemId), count(ItemId) as 'FailCount'
FROM TestResults a
inner join TestResults b on a.ItemId = b.ItemId and a.TestID = b.TestId
WHERE TestFail = TRUE
group by ItemId, TestId having count(ItemId) >= 3

 

Would be more efficient. I am not sure if the above would work in access. Though if you remove the bits that don't help the results in your existing query it should work a bit quicker:

SELECT count(ItemId)
from TestResults a
where ResultId IN (
SELECT Top 3 ResultID from TestResults b Where a.ItemId = b.ItemId AND a.TestID = b.TestId ORDER BY TestDate DESC )
AND TestFail = true
)
group by ItemId, TestId having COUNT(ItemId) >= 3