Access: Bottom 20% of Performers by Average Performance?

Question

So I have been tasked with using an Access database to find the bottom 20% of performers every week. I am having a mental block though on how to do that given that every worker will essentially have 5 days worth of data, so I need to find the bottom 20% by average and not by individual days. Is there a way to do this in Access? Or would I have to take an excel database and find an average for each person via something like a pivottable then export that to Access to find the bottom 20%?

Thank you for any help!

Answer 1

I think you need to filter to get the correct date range, then group by person to get an average score for that week, then you can use the TOP ## PERCENT statement in to get the 20% you need

Select TOP 20 PERCENT Person, AverageScore
FROM (
    Select Person, AverageScore = Average(Score)
    FROM YourTable
    WHERE YourDateColumn > dateadd("d", -7, date) and YourDateColumn <= date
    Group By Person
    ) avgs
order by AverageScore desc