This this possible in a query?

[tcshred]

I have a table which has the following information:
Name
Team
Date
Score (as a decimal/%)

Is it possible to have a query which would look similar to this, where each % is the average for that team during that month:

Team September 13 October 13 November 13
Team A 100% 95% 88%
Team B 50% 55% 99%
Team C 81% 100% 79%

I have about 10 teams with about 10 people in each. I am trying to create some kind of summary query.

 

[tcshred]

hmm for some reason my formatting didnt work out. Hope it is still understandable...

 

[namliam]

Look into a Crosstab query, that should do that.

 

[plog]

This is more than just a simple Cross-tab query (and cross-tab queries are anything but simple to begin with).

First, 'Name' and 'Date' are horrible field names--they are reserved words in Access and make working with them in code and queries a little difficult. I suggest prefixing them with what the data is for (i.e. TeamName, GameDate, etc).

To accomplish what you want, I would first start with a select query that gets your data fields where you need them to be. You need to group by Team, Group by Month, Group by Year and Average the Score. So that query will look like this:

SELECT Month([Date]), Year([Date]), Team, AVG(Score)
FROM YourTableNameHere
GROUP BY Month([Date]), Year([Date]), Team;

That should get you 90% there. Next you will have to use the MonthName function (http://www.techonthenet.com/access/functions/date/monthname.php) to get the month names and then concatenate the year to it to get the format you exactly specified. Once you have that, save the query, make a copy of it and then try and turn it into a cross-tab (http://office.microsoft.com/en-us/a...ad-by-using-a-crosstab-query-HA010229577.aspx).

 

[namliam]

Why not use the format function, instead of Month() wrapped into MonthName and concatted with year. Plus you can do the AVG of your query inside the Cross-tab, which does take some getting used to but in the end (IMHO) is simple but I can imagine for people not used to it, it can be quite daunting.