slow group by in query

[viwaltzer]

in the following query when I just run the inside query, it runs instantaneously. I get about 19,000 records. When I run the query grouping the data, it takes about 9 seconds. Can someone please let me know why it's so slow and how I can make it faster.

SELECT year, event_id, first(Date_Start) AS dateStart, first(event_State) AS eventState, state, ageDesc, stateName, count(*) AS recCnt
FROM (SELECT Year(event.Date_Start) AS [year], event.event_id, Event.Date_Start, Event.state AS event_State, IIf(tests.state='' Or IsNull(tests.state)
or tests.state not in (select [state abbreviation] from states where [state abbreviation]<>'' and not isnull([state abbreviation]))
,event.state,tests.state) AS state, Tests.age, iif(tests.age<20,'18 to 20',iif(tests.age<30,'20 to 29',iif(tests.age<40,'30 to 39',iif(tests.age<50,'40 to 49',
iif(tests.age<60,'50 to 59',iif(tests.age<70,'60 to 69',iif(tests.age<80,'70 to 79','80 or more'))))))) AS ageDesc, StatesTests.[state name] AS stateTests, StatesEvent.[state name] AS stateEvent, IIf(tests.state='' Or IsNull(tests.state) or tests.state not in (select [state abbreviation] from states where [state abbreviation]<>'' and not isnull([state abbreviation])) ,statesEvent.[state name],statestests.[state name]) AS stateName
FROM ((Event INNER JOIN Tests ON Event.Event_ID = Tests.Event_ID) LEFT JOIN States AS StatesTests ON Tests.state = StatesTests.[State Abbreviation]) INNER JOIN States AS StatesEvent ON Event.State = StatesEvent.[State Abbreviation]) AS q1
GROUP BY year, event_id, state, ageDesc, stateName;


I'm using access 2010. Thanks much

 

[Guus2005]

Loose the nested IIF's they are slowing the query down.
Your nested "where not in (subquery)" is not helping either.

Create a table TestsAge with two columns age and range.
age should be filled with all possible ages.
range should be filled with matching range texts.

18, 18 to 20
19, 18 to 20
20, 20 to 29
21, 20 to 29
etc.

Joining this table is a lot faster.

Split this query up into smaller queries each solving a part of the problem.
Then combine them together to create the end result.
This gives you better understanding of what is happening and it is easier to read and maintain.

HTH