Discussion Locked

Get Average in Access for Skills Rank

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Say you play pool one night a week, and so do 200 other people on a league. Ok, input your scores week after week into a database. Now, your average is games played vs. games won. But your skills increase with time, so what you want is your average of the last 20 weeks at any given time? If games played > 20, then avg(last 20 games played) how?
Cindy

9 replies

by tedstidham

Cindy:
You can use nested queries. Your primary query needs to be designed in SQL view... Use something like "Select TOP 20 scores.pool....". Use this query as your source table and then use your average function in your secondary query.Ted

by aekland

Run a queary that select the scores for people using something like ">Now()-146 (20 weeks plus 6 days)" This will give you the most recent 20 scores. Then you can run a report or another query that will produce the average for the weeks. You can usethe count expression to count the scores incase anyone is just starting or misses a week.

by cindy - extraordinary brat

I still can't do this. The players sometimes play 3 times a week, someone doesn't show up and plays 2 times a month. Some people start in the middle of a session. I need the query to work without dates as a factor in selecting scores. I dont' want atable for each player, but the concept of an autonumber applied to each user's scores would give me their most current 20 with a query. See where I'm going?

This discussion is closed to new replies.