query for Report
I am building a database for a sports competetion. The data in the table looks like this:
Team Name Judge Score
Team 1 1 10
Team 1 2 10
Team 1 3 10
Team 2 1 20
Team 2 2 20
Team 2 3 20
Team 3 1 30
Team 3 2 30
Team 3 3 30
I need the report to look like this:
Team Name Judge1Score Judge2Score Judge3Score
Team 1 10 10 10
Team 2 20 20 20
Team 2 30 30 30
How can I build a query to make the report look like the above? Thanks!
Team Name Judge Score
Team 1 1 10
Team 1 2 10
Team 1 3 10
Team 2 1 20
Team 2 2 20
Team 2 3 20
Team 3 1 30
Team 3 2 30
Team 3 3 30
I need the report to look like this:
Team Name Judge1Score Judge2Score Judge3Score
Team 1 10 10 10
Team 2 20 20 20
Team 2 30 30 30
How can I build a query to make the report look like the above? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this crosstab query
TRANSFORM First(Table2.Score) AS FirstOfScore
SELECT Table2.Team
FROM Table2
GROUP BY Table2.Team
PIVOT "Judge" & Table2.Judge & "Score";
TRANSFORM First(Table2.Score) AS FirstOfScore
SELECT Table2.Team
FROM Table2
GROUP BY Table2.Team
PIVOT "Judge" & Table2.Judge & "Score";
TRANSFORM Sum(tblReport.Score) AS SumOfScore
SELECT tblReport.Team
FROM tblReport
GROUP BY tblReport.Team
PIVOT tblReport.Judge;
Change the tblReport to your actual Table Name and the FieldNames to your actual FieldNames.
Set Team as RowHeader, set Judge as ColumnHeader, and set Score as Value.
Hope this helps,
Daniel