How to?? Exclude top 5% and bottom 5% and calculate an average per

[Alex]

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

 

[Duane Hookom]

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)