MS Access TRIMMEAN how to

Question

I need to perform TREAMMEAN in Access, which does not have this function.

In a table I have many Employees, each has many records. I need to TRIMMEAN Values for each Employee separately.

Following queries perform TOP 10 percent for all records:

qry_data_TOP10_ASC

qry_data_TOP10_DESC

unionqry_TOP10_ASCandDESC

qry_data_ALL_minus_union_qry

After that, I can use Avg (Average).

But I don't know how to do it for each employee.

Visualization:

Note: This question is edited to simplify problem.

Why don't you just make a query that averages your field from qry_data_minus_TOP10? — Jeffrey, Mar 27 '18 at 18:32
Be aware that in a query one Employee has many records. And there are many employees. After aplying filters City and TimePeriod, result should be a list where one Employee will have a single record, and a value will be trimmed and averaged from all records of that employee within selected TimePeriod. Something like grouping in a report where I have Group on Employee, and Sort by Value: you get a list with a single record for each employee. — Vedran, Mar 28 '18 at 13:59
It still sounds like you should still be able to do this with queries, subqueries, and aggregate queries. You just have to do them in the correct order. — Jeffrey, Mar 28 '18 at 15:35
I did it but for all records. How to do it for each employee? — Vedran, Aug 27 '18 at 09:46
Still, TOP 10 PERCENT will cut off top ten from ALL records, not TOP 10 from each employee. If there are 100 records and each Employee has 10 records, it will cut off all 10 records from first employee.. — Vedran, Aug 27 '18 at 14:23
If I group it by Employee, I will have AVG of all Values, without TPO 10 and TOP 10 DESC, what I need. What I see is that I need to sort by Employee (employeeID), then to apply TOP 10 for first EmployeeID, and so on to the EOF. In a subquery I coud do Averaging of the results, as you suggested. Missed something? — Vedran, Aug 28 '18 at 12:46
https://stackoverflow.com/questions/21059077/ms-access-select-top-n-query-grouped-by-multiple-fields Does this help? — Jeffrey, Aug 28 '18 at 16:15
Nope. I created MyTable as described and copied a code; result is empty query. Still, thanks for finding it. Anyhow, I'm still stuck with it. — Vedran, Sep 13 '18 at 08:24

Jeffrey · Answer 1 · 2018-09-14T00:34:20.197

This should give you what you want, the two sub-queries should correlate the TOP 10s (ascending and descending) for every employee. The two NOT INs should then remove those from the Table1 records and then you group the Employees and Average the Scores.

SELECT Table1.Employee, AVG(Table1.Score) AS AvgScore
FROM Table1
WHERE ID NOT IN
(
    SELECT TOP 10 ID
    FROM Table1 a
    WHERE a.Employee = Table1.Employee
    ORDER BY Score ASC, Employee, ID
)
AND ID NOT IN
(
    SELECT TOP 10 ID
    FROM Table1 b
    WHERE b.Employee = Table1.Employee
    ORDER BY Score DESC, Employee, ID
)
GROUP BY Table1.Employee;

score 0 · Answer 2 · answered Apr 10 '18 at 18:30

You don't really give information in your pseudo code about your data fields but using your example that DOES have basic field information I can suggest the following should work as you described

It assumes field1 is your unique record ID - but you make no mention of which fields are keys

SELECT AVG(qry_data.field2) FROM qry_data WHERE qry_data.field1 NOT IN
(SELECT * FROM 
   (SELECT TOP 10 PERCENT qry_data.field1, qry_data.field2
   FROM qry_data
   ORDER BY qry_data.field2 ASC)
   UNION
   (SELECT TOP 10 PERCENT qry_data.field1, qry_data.field2
   FROM qry_data
   ORDER BY qry_data.field2 DESC)
)

Please note that I edited the question for simplification. This works for all records. But how to do it for each Employee separately? A code has to remove TOP 10 for first employee, then for the second and so on. After that I need to Average all valeus ofr first employee, then for the second and so on. PS: Why formating with spaces doesn't work in commenting? — Vedran, Aug 27 '18 at 09:43

MS Access TRIMMEAN how to

2 Answers2