Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Query to sum and average scores

    I have a table in Access 2010 with data like this (the score field contains one of five values, A-E).

    [Event], [Name], [Score]
    1, Tom, A
    2, Dick, E
    3, Harry, B
    4, Dick, D
    5, Harry, B
    6, Tom, C
    ... etc.

    I want to create a query that will group the names and sum the scores, like this.

    [Name], [A], [B], [C], [D], [E]
    Dick, 0, 0, 0, 1, 1
    Harry, 0, 2, 0, 0, 0
    Tom, 1, 0, 1, 0, 0

    If possible, I also want to calculate an overall score for each name, by allocating a value to each score and averaging them, like this (A=1, B=2, C=3, D=4, E=5, so for example, 1xA and 2xB and 1xD would be 1x1 + 2x2 + 1x4 = 9/4 = 2.25).

    [Name], [A], [B], [C], [D], [E], [Overall]
    Dick, 0, 0, 0, 1, 1, 4.5
    Harry, 0, 2, 0, 0, 0, 2
    Tom, 1, 0, 1, 0, 0, 2

    What is the best way to do this?

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi
    I think that the pivot views on tables and the ability to create pivot queries in Access might help.

    Then there is the ability to export to Excel and use the Pivot table functionality within Excel.

    PowerPivot might be another really powerful tool for you to play with. This is the free Excel add-in. That lets you connect directly to the database and introduces an expanded range of functions as well as DAX (Data Analysis Expressions).

    See more about DAX here.

    Cheers
    G

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I gather I will need to use a crosstab query in Access, but I am hoping for some help with setting it up.

  4. #4
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    I have created this may help:

    Dynamic Report based on a Crosstab query
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Sample Cross Tab Query

    Quote Originally Posted by Murgatroyd View Post
    Thanks for your reply. I gather I will need to use a crosstab query in Access, but I am hoping for some help with setting it up.
    Hi
    Database :NorthWind 2007.
    Query Source: Product Sales Category, a query in the database

    The accompanying sql is from a xTab query against an existing query in NorthWind 2007
    I used the cross Tab Query Wizard initially. Then added the average in the query grid.

    Cross Tab queries have the verbs Transform and Pivot in their setup.

    Code:
    TRANSFORM Sum([Product Sales by Category].Amount) AS SumOfAmount
    SELECT [Product Sales by Category].[Product Name], Sum([Product Sales by Category].Amount) AS [Total Of Amount], Avg([Product Sales by Category].Amount) AS [Avg Of Amount]
    FROM [Product Sales by Category]
    GROUP BY [Product Sales by Category].[Product Name]
    PIVOT [Product Sales by Category].Category;
    xTabQuery.png

    Someone with more skills that i will need to help you get the additional calculations into this query.

    You might be able to use nested IIF() functions in the query design grid to create and assign a value based on grade. Subsequently that new value could be used in another calculation. The query grid field row for this might look like this

    newLabel:IIF(fieldname = "?", value. IIF(, ,, ))
    This is a bit like nesting IF() functions in excel.

    the sql portion will look something like this
    IIf([fieldName]="?",x,IIf(fieldName="?",y,z)) AS NewLabel


    Over to someone more skilled than me.
    Regards
    Geof
    Last edited by geofrichardson; 2015-08-06 at 16:35.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your further replies. After some experimentation, I managed to get the result that I wanted, by creating a crosstab query to display the scores for each Name in columns, and a total query to calculate an overall score for each Name, then creating a third query joining these two queries on the Name field.

  7. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Did you look at my sample? It shows a way to do it with a single query.

    I used this as a template a lot. You can import the report. Change the name. Change the query name to you crosstab query .... Pow ... it works.
    Last edited by HiTechCoach; 2015-08-10 at 15:07.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  8. The Following User Says Thank You to HiTechCoach For This Useful Post:

    geofrichardson (2015-08-07)

  9. #8
    New Lounger
    Join Date
    Jan 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for your further replies. After some experimentation, I managed to get the result that I wanted, by creating a crosstab query to display the scores for each Name in columns, and a total query to calculate an overall score for each Name, then creating a third query joining these two queries on the Name field.
    I wonder if this query would do what you were looking for, not sure access can handle it, but it is what I would have tried.

    SELECT [name],
    SUM(CASE WHEN score='A' THEN 1 ELSE 0 END) AS A,
    SUM(CASE WHEN score='B' THEN 1 ELSE 0 END) AS B,
    SUM(CASE WHEN score='C' THEN 1 ELSE 0 END) AS C,
    SUM(CASE WHEN score='D' THEN 1 ELSE 0 END) AS D,
    SUM(CASE WHEN score='E' THEN 1 ELSE 0 END) AS E,
    AVG(CASE WHEN score='A' THEN 1
    CASE WHEN score='B' THEN 2
    CASE WHEN score='C' THEN 3
    CASE WHEN score='D' THEN 4
    CASE WHEN score='E' THEN 5 END) AS Overall
    FROM ScoreTable
    GROUP by [name]

    Bill

Posting Permissions

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