Query Help: Averages per user and period

[Gabriel2012]

Greetings, I have a table with multiple fields, those which are relevant for my query are listed below. I need to create a query that determines the average score for each user by period. Below is an example of my intended outcome, any help is appreciated, thank you.

Example Table layout:
LastName, Period (month), Score

Bob 1 100
Bob 1 50
Bob 1 100
Jim 2 100
Jim 2 50
Jim 2 100

Expected Query Result:
LastName, Period (month), Score Average

Bob 1 75
Jim 2 75

[jzwp11]

You need an aggregate query. The SQL text would look like this

SELECT lastname, period, Avg(Score)
FROM tablename
GROUP BY lastname, period

If you are using the design grid view, you would add the three fields and then right click the column with the score field; select Totals. In the Totals row of the grid, change the Group By for the score field to Average.

[alansidman]

Here is a SQL statement. Change field names and table names to yours.

SELECT Scores.FName, Scores.Period, Avg(Scores.Score) AS AvgOfScore
FROM Scores
GROUP BY Scores.FName, Scores.Period;

[Gabriel2012]

Bravo, thank you.