[RESOLVED] Grouping and sorting results

[easymoney]

I am using Data Designer with the following SQL query with MS Access 2002:

SELECT DISTINCTROW Format$([Transactions].[Cycle],'mmm/yyyy') AS [Cycle By Month], Transactions.Order, Transactions.Transaction, Sum(Transactions.Amount) AS [Sum Of Amount], Count(*) AS [Count Of Transactions]
FROM Transactions
GROUP BY Format$([Transactions].[Cycle],'mmm/yyyy'), Transactions.Order, Transactions.Transaction, Year([Transactions].[Cycle])*12+DatePart('m',[Transactions].[Cycle])-1
HAVING (((Format$([Transactions].[Cycle],'mmm/yyyy'))="Sep/2006"));

The data report is populated with data but I am trying to add sort to the [Sum of Amount].

GROUP BY Transactions.[Order]

but add a second level SORT Transactions.[Sum of Amounts] from greatest to least.

Any ideas how I can force data designer in vb6 or the sql query to return the results in the proper order?

[ganeshmoorthy]

Add this piece of code at the end of your sql statement

Sort By Sum(Transactions.Amount) Dec

[easymoney]

I had tried adding the SORT, but maybe I added it in the wrong place of the SQL statement. The sort would override the primary sort of Transactions.[Order].

I want to sort the query results by Transactions.[Order] first, then perform a secondary sort by [Sum of Amount].

for example this is an exampleof the data I get now:

Order     Transactions            [Sum of Amounts]
=====================================
  0         Applied Service Charge          (-425.00)
  0         Service Charge                      $500.00
 10        Installation Charge                  $125.00
 10        Applied Service Charge          (-101.25)

I want the query to return the data as such (Sort By Order,then Sort by [Sum of Amounts:] Descending, etc...

Order     Transactions            [Sum of Amounts]
=====================================
  0         Service Charge                      $500.00
  0         Applied Service Charge          (-425.00)
 10        Installation Charge                  $125.00
 10        Applied Service Charge          (-101.25)

I will try again placing the sort add theend as you suggested. Will post back. Thanks.

[easymoney]

We'll you put me on the right track. I keep getting sql errors using SORT, but adding this to the end did the trick:

ORDER BY Transactions.Order, Sum(Transactions.Amount) DESC;

Thanks for the inspiration....