I am trying to create a query in access that will do the following (and I am stuck):
Take 3 numbers for a person and average those togther.
The part I am stuck on is the initial results are for about 17 diffrent people. So I have 5 columns and the last column is the "Score" column. I need to average this column on a per-person basis. Every query I try to write is just averaging everything. Any help would be appreciated.
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!
Using query wizard: After you created query, open it in design window, and enter the person's identifier (or some text like 'Person' in square brackets, when you want to be asked for person, when query is run) into Criteria row of according column;
Or in SQL view, add a HAVING clause to query string like:
SELECT table.field2, ... , AVG(table.field5) AS Avg5 FROM table table GROUP BY table.field2, table.field3, table.field4 HAVING table.field1='John';
Or in SQL view, add a WHERE clause to query string like:
SELECT AVG(field5) AS Avg5 FROM table WHERE field1='John'
Or, when you want to get averages for all persons
Select field1, field5 FROM table GROUP BY field1