ACCESS 2010 SQL-- using WHERE IN on SELECT TOP subquery field
ms-access-2010, select, sql, subquery, where-clause
Solution
I suspect that this is just a limitation of Access's optimizer. Try it like this instead:
SELECT d.[Company],
Sum(d.[SPENDING])
FROM [Data] As d
INNER JOIN (
SELECT TOP 10 [Company]
FROM [Data]
WHERE [Year] IN ("2013")
GROUP BY Company
ORDER BY Sum([SPENDING]) DESC
) As t ON t.Company = d.Company
WHERE d.[Year] IN ("2012")
GROUP BY d.Company
Problem
I want this query to tell me the spending in 2012 of the companies who were the top 10 spenders in 2013! ``` SELECT [Company], Sum([SPENDING]) FROM [Data] WHERE [Company] IN ( SELECT TOP 10 [Company] FROM [Data] WHERE [Year] IN ("2013") GROUP BY Company ORDER BY Sum([SPENDING]) DESC ) AND [Year] IN ("2012") GROUP BY Company ; ``` When I try to run it, I get no errors, but Access says it is "running query" and never finishes. The size of the data is not the problem. This is the closest example I found, but it doesn't really give light to the answer: MS Access - WHERE IN works, but WHERE NOT IN fails