In a query, I have dates (1st row), and a number (2nd row). 

date1 -- 105
date1 -- 90
date1 -- 120
date2 -- 89
date2 -- 107
date2 -- 99
....

I need to calculate the average of those numbers for each date. It's easy to 
do using the GROUP BY feature, then group by date, AVG the number. 

However, for each date, I need to filter out the top and bottom 5%. I know 
how to do this in a regular query, but I don't know how to do it here since 
that 'filter' needs to happen several times (once for each date) in the 
query. 

Any idea? 
Alex

I think you could do this using a subquery in your criteria. Do you have a 
primary key field in your table? Could you share your actual table and field 
names?

The where clause with a subquery would be  something like
WHERE PKField Not In (SELECT Top 5 PERCENT PKField FROM tblA A WHERE 
A.DateField = tblA.DateField ORDER BY NumField)
-- 
Duane Hookom
Microsoft Access MVP


"Alex" wrote:

> In a query, I have dates (1st row), and a number (2nd row). 
> 
> date1 -- 105
> date1 -- 90
> date1 -- 120
> date2 -- 89
> date2 -- 107
> date2 -- 99
> ...
> 
> I need to calculate the average of those numbers for each date. It's easy to 
> do using the GROUP BY feature, then group by date, AVG the number. 
> 
> However, for each date, I need to filter out the top and bottom 5%. I know 
> how to do this in a regular query, but I don't know how to do it here since 
> that 'filter' needs to happen several times (once for each date) in the 
> query. 
> 
> Any idea? 
> Alex