Sort By Derived Number (Group Footer) In Report

[metsey]

Example : I have 3 fields in a table

GroupNo Profit NoEmployees
1 $5 2
1 $1 1
2 $2 2
2 $0 2

I want report to appear as such

Group Profit NoEmployees
1
$5 2
$1 1
totals $6 3 Profit Per Emp $2
2
$2 2
$0 2
totals $2 4 Profit Per Emp $.50

But I want to sort either by ascending ProfitPerEmp or descending Profit Per Emp

Is this possible?

 

[StarPassing]

Yes, it can be done. You can do it in more elaborate ways, but to keep it simple, try this.
Assume your table name is ‘Test’ .
Set up 3 queries as follows:
Query Name: Groups
SQL: SELECT Test.GroupNo, Test.Profit, Test.NoEmployees FROM Test;

Query Name: SumOfGroups
SQL: SELECT [Test].[GroupNo], Sum([Test].[Profit]) AS SumOfProfit, Sum([Test].[NoEmployees]) AS SumOfNoEmployees
FROM Test
GROUP BY [Test].[GroupNo];

Query Name: EndResult
SQL: SELECT First(Groups.GroupNo) AS FirstOfGroupNo, Groups.Profit, Groups.NoEmployees, Sum(SumOfGroups.SumOfProfit) AS SumOfSumOfProfit, Sum(SumOfGroups.SumOfNoEmployees) AS SumOfSumOfNoEmployees, Sum([SumOfProfit]/[SumOfNoEmployees]) AS ProfitPerEmp
FROM SumOfGroups INNER JOIN Groups ON SumOfGroups.GroupNo = Groups.GroupNo
GROUP BY Groups.Profit, Groups.NoEmployees;


Create report based on query EndResult. Set the reports Sort and Grouping to ‘ProfitPerEmp’ and ‘Firstof GroupNo’. The latter will have a header and footer.

To ask for accending/descending, place the following in the report:
Private Sub Report_Open(Cancel As Integer)
If MsgBox("Sort Accending ?", vbYesNo + vbQuestion, "Sort Order&quot = vbYes Then
‘ sort the ProfitPerEmp’ to ascending
Me.Report.GroupLevel(0).SortOrder = False
‘ sort the ProfitPerEmp’ to descending
Else
Me.Report.GroupLevel(0).SortOrder = True
End If
End Sub

If needed, send email address and I’ll send you the sample report db.

Tom