Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

query for Report

Posted on 2011-09-20
3
Medium Priority
?
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

670 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