Solved

query for Report

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

773 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