AVG iif Function in a Query

[STRATMAN1]

I have created a query where I try to return six records that correspond to six states. For each record, I am trying to create an expression in the query that returns the average of one field , with a criteria limiter, for each of the six states. I have used Avg iif to do this. I cannot get the function to correctly compute the average of the records in the table that correspond. For example, if there are only two records from a total of 10 that should be averaged, the avg function computes the average over the entire 10. I am setting the criteria to consider only two as I need. Have also tried to set the criteria via the criteria row and the where aggregate function, repeating the field but result is not right.

When I try Davg using correct number of arguments, I get the same average for all six states, i.e same result in every row, not differentiating the state.

Cant seem to identify my error. All good advice appreciated.Thanks!!!

 

[dRahme]

Something like this?

SELECT Table1.State, Table1.Age, Avg(Table1.population) AS AvgOfpopulation
FROM Table1
GROUP BY Table1.State, Table1.Age
HAVING (((Table1.Age)="Over50"));