[RESOLVED] Grouping and sorting results

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

VB Code:

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?

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

VB Code:

Sort By Sum(Transactions.Amount) Dec

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:

Code:

---

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...

Code:

---

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.

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:

Code:

---

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

---

Thanks for the inspiration.... :wave: