How do I make a report sort by calculated fields ?

[theSizz]

I can't get my report to sort based on calculated fields.
Here's the SQL that the report is linked to.

SELECT [Team Data].[Team Points], [Team Data].[Opp Points], [team points]+[opp points] AS GameTot
FROM [Team Data];

The report has 3 text box controls in the group header. [GameTot] which has the select query as its control source. Then there are 2 calculated controls named [games] and [pct]. The formula in [games] is Count(*) and the formula in [pct] is =[games]/[TotGames]. [TotGames] is a calculated control in the report footer and contains the formula Count(*). I want to be able to sort by the calculated control [pct].Placing [pct] in the sorting and grouping box justs brings up a dialog box that asks for the parameter value of [pct]. Can anyone tell me how to make this sort by the [pct] field. Does it have something to do with I'm trying to sort by an aggregate function ? Any help would be appreciated. Thanks !

 

[danvlas]

As far as I know, you can only sort the report by a field coming from the recordsource. You may try including the calculation in the query and the result will be available for sorting the report. But I may be wrong...

HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[MazeWorX]

Think of the logic behind the report you have created. Your 2 calculated values are based on the results at the report level therefore they do not exist prior to the report running. Daniel is correct when he says add them at the query level. Remember amateurs built the ark - professionals built the Titanic