Solved

query for Report

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

617 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