Reporting query data 1

[TrekBiker]

I'm generating team scores in a query with fields Team and Score that outputs like this

Team A / 360
Team B / 356
Team C / 320
Team D / 380

For each team I'd like to add the group average and team's score relative to the winner and use the data in separate reports for each team. The first report would say :

Team A score, Position 2, Score 360, Group average 354, You scored 20 less than the winner.

 

[dhookom]

You could add a text box [txtPosition] with a control source of =1. Set the Running Sum property to Over All. Sort the report in order of score descending. Add a text box to the detail section with a control source like:

=[Team Name] & " score, Position " & [txtPosition] & ", Score " & [Score] & ", Group average " & 
Avg([Score]) & ", You scored " & Max([Score]) - [Score] " & less than the winner."

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Many thanks Duane.

This is great in a report structure and moves me forward a lot.

If it were possible I'd ideally like to have the same calculated fields (Max, Average etc) appear in query format so I could compound the data for teams that compete in different groups.

So fields would be Group, Team, Score, Position, Average Score, Max Score. I have all of these except for the last two.

I could then create a report or mail merge for each Team saying

Your results in group 1 were
Position
Score
Average for group
You scored x less than the winner

In group 2
Position
Score
Average for group
You scored x less than the winner

 

[PHV]

I have all of these except for the last two
Coul you please post your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrekBiker]

I simplified the data in my question so here's the full picture.

The starting table called FullResults contains results from score sheets where a number of judges score companies over 6 criteria (2 to 7) and a number of categories. Fields are

Category
Company
2
3
4
5
6
7

A first query called Best calculates Total Score and Average Score for a chosen Category

SELECT FullResults.Category, FullResults.Company, FullResults.[2], FullResults.[3], FullResults.[4], FullResults.[5], FullResults.[6], FullResults.[7], FullResults.[8], [2]+[3]+[4]+[5]+[6]+[7] AS TotalScore, IIf([2]>0,1,0)+IIf([3]>0,1,0)+IIf([4]>0,1,0)+IIf([5]>0,1,0)+IIf([6]>0,1,0)+IIf([7]>0,1,0) AS NumberScores, [TotalScore]/[NumberScores] AS AverageScore
FROM FullResults
WHERE (((FullResults.Category)=[forms]![Categories].[Category]))
ORDER BY [2]+[3]+[4]+[5]+[6]+[7] DESC;

Then queries extract data for each of the 6 criteria and calculate totals, eg for Criterion 2

SELECT Best.Category, Best.Company, Sum(Best.[2]) AS SumOf2, "2" AS Criterion
FROM Best
GROUP BY Best.Category, Best.Company, "2"
ORDER BY Sum(Best.[2]) DESC;

The query output shows

Category, Company, Total (ie SumOf2) and Criterion Number.

In reporting to this company, as well as stating their total score and ranking I'd like to be able to give the average and maximum for all companies in the set.

 

[dhookom]

I would start by changing your table structure to one that each score created a record. This would allow for more criteria and easier aggregating.
tblScores
Category
Company
Criteria
Score

If you can't or won't change the structure, you can create a normalizing union query

SELECT Category, Company, 2 as Crit, [2] as Score
FROM FullResults
UNION ALL
SELECT Category, Company, 3, [3]
FROM FullResults
UNION ALL
SELECT Category, Company, 4, [4]
FROM FullResults
UNION ALL
SELECT Category, Company, 5, [5]
FROM FullResults
UNION ALL
SELECT Category, Company, 6, [6]
FROM FullResults
UNION ALL
SELECT Category, Company, 7, [7]
FROM FullResults

It's surprising how easy it can be to create sums and averages in standard totals queries when you structure is normalized.



Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Point well made, thanks Duane.

The scoring results come from a website in the format I showed but your union query will arrange the data more sensibly.