[AccessD] Sort order in report

Change your sort order in the report to SumOfAmount desc, Customer.Name to
get the result you desire.
 
Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com <http://www.hargroveinc.com/> 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ervin Brindza
Sent: Wednesday, September 03, 2003 7:26 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Sort order in report


Hi,
I need a little push to finish my report. I have 3 tables: Customer(Id,
Name), Order(Id - the customer's Id, KeyFld - AutoNumber field, linked to
the table OrderDet), OrderDet(KeyFld, Id - ordered item's Id, ItemName,
Amount)
The record source of the report is:
SELECT CUSTOMER.Id, CUSTOMER.Name, ORDERDET.ItemName, ORDERDET.Id,
Sum(ORDERDET.Amount) AS SumOfAmount

FROM CUSTOMER INNER JOIN (ORDER INNER JOIN ORDERDET ON ORDER. KeyFld =
ORDERDET.KeyFld) ON CUSTOMER.Id = ORDER.Id

GROUP BY CUSTOMER.Id, CUSTOMER. CustName, ORDERDET.ItemName, ORDERDET.Id;

And I want to list the customers along with their ordered items, but in
descending order on the sum of amount they ordered(the ordered items are
listed desc and that is ok). In the report's View/Sorting and grouping I put
CUSTOMER.Name and SumOfAmount desc and I get customers listed by their name
:(

So, my report looks like:

1. Ervin Brindza

    1. orange 100

    2. apple 80

2. Mike Thompson

    1. plum 155

    1. orange 40

but I want Mike Thompson to be listed first(he ordered 195) and after came
Ervin Brindza(who ordered 180)

Hope I was clear enough.

Can anyone shed any light please?

TIA,

    Ervin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030903/c4e8ec6f/attachment-0001.html>