Report Sort Problem

  • Thread starter Thread starter Harv
  • Start date Start date
H

Harv

Hi, can anyone help with a problem I have?
I have a report that I want to sort on an expression.
Here is an example of what I have. I have got three
Fields, which are,

Namefield Homescorefield Awayscorefield

Name1 21 11
21 16
17 21
SumOfHomescore 59 SumOfAwayscore 38 average 1.552

Name2 21 12
21 15
21 18
SumOfHomescore 63 SumOfAwayscore 45 average 1.4

The report as a name header & name footer and the header
is grouped on the name field. The footer has three texts
Boxes, one sums the homescore, one to sum the awayscore
(between certain dates) & one called average which
divides SumOfHomescore by SumOfAwayscore.
This works well. My problem is that I want to sort on the
Average text box instead of the Namefield.


Thanks Harvey
 
Create and save a totals query
SELECT Namefield, Sum(Homescorefield)/Sum(Awayscorefield) As TheAvg
FROM tblYourScores
GROUP BY Namefield;

Then add this query to your report's record source query and join the
Namefields. You can then sort on TheAvg. You may need to add your date
filter on the totals query.
 
Duane. I have just tried to find a totals query. Is it
something special? I am running 2002.
 
Back
Top