Query to sum and average scores

Topic

New Reply

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], , [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 1×1 + 2×2 + 1×4 = 9/4 = 2.25).

[Name], [A], , [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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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;

41586-xTabQuery

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

Reply | Quote

I have created this may help:

Dynamic Report based on a Crosstab query

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote