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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 23 Feb 2008 05:42 GMT

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

Reply to this Message

Duane Hookom - 23 Feb 2008 23:42 GMT

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)

Signature

Duane Hookom
Microsoft Access MVP

> In a query, I have dates (1st row), and a number (2nd row).
>
[quoted text clipped - 16 lines]
> Any idea?
> Alex

Reply to this Message