Solved

query for Report

Posted on 2011-09-20
3
287 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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