Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    40

    Unanswered: Average in query

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    are the values in one record (several fields) or in several records (same column?

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Average in query

    SELECT PersonID, AVG(Score)
    FROM Table1
    Group BY PersonID
    http://www.scirocco.ca/images/banner...occobanner.gif

    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!

  4. #4
    Join Date
    Mar 2004
    Location
    Estonia
    Posts
    3
    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

    Or any working combination of examples above.

  5. #5
    Join Date
    Dec 2003
    Posts
    40

    Talking

    Thanks so much. All your advice made it work

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •