Solved

query for Report

Posted on 2011-09-20
3
283 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:Sasha42
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36567823
Use the crosstab query wizard to create a crosstab with Team Name as the row headings and Judge as the column headings.

Then, create a report based on that crosstab query.
0
 
LVL 12

Expert Comment

by:danishani
ID: 36567860
Try this SQL, creating a CrossTab Query:
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36567861
try this crosstab query

TRANSFORM First(Table2.Score) AS FirstOfScore
SELECT Table2.Team
FROM Table2
GROUP BY Table2.Team
PIVOT "Judge" & Table2.Judge & "Score";
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now