Sort by Grouped calculated field?

[Alexcamp via AccessMonster.com]

My boss has asked me to make him a complicated report. I use Crystal Reports
most of the time but for this report I need to make some queries in access
first.

Basically here's what he wants:

TOP N customers based on last year's total deliveries, + column for this
year's orders taken so far, + column for delivered orders so far, + two
column which are just sums/subtractions from previous columns.

What I want to do in Access is the TOP N query alone. To get the total
deliveries I use my ordersdetail table and just do a QTY*UnitPrice and then
group everything by customer and sum this calculated total. Here's my SQL
string:

SELECT Commande.SoldTo_com, Sum([Total_cpr]*[PrixUnite_cpr]) AS TOTAL
FROM (Commande LEFT JOIN Expedition ON Commande.NoCommandeSeq_com =
Expedition.NoCommandeSeq_exp) LEFT JOIN CommandeProduit ON Commande.
NoCommandeSeq_com = CommandeProduit.NoCommandeSeq_cpr
WHERE (((Expedition.LoadingDate_exp) Between #9/1/2006# And #8/31/2007#))
GROUP BY Commande.SoldTo_com;

Now I am trying to sort this and I just can't.
What I would like is to sort DESCENDING based on the grouped calculated total.
I would also like to add a field where a sequential number would be assigned
to each record ONCE sorted.

eX:
1 | BigCompany | 45776 $
2 | AdventureWorks | 42334 $
3 | Smallcompany | 23556 $

and so on....

So is all this possible ?? :-|

Thanks for any help

Alex

 

[Rob Ward]

I don't know Crystal Reports but this would be trivial for an Access report.
There's no reason why you can't sort the Access query by the calculated field
so that the records are presented in the correct order; and you can select
just the top 'n' records in the query as well, by changing the query
properties.

You can't *easily* sequentially number rows in a query, though there are
some flaky workarounds; in an Access report you can do it easily by creating
a text box control, setting it's control source to '=1', then setting the
Running Sum property to Over All.