[AccessD] Sort order in report

Sorry...missed the fact that the sum you were using wasn't the total amount
of items they had ordered.
 
The only way to do what you want is to create a main report that handles the
"Customer/Total Amount" sorting and then add a subreport to list the detail
records for that Customer.
 
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 8:17 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Sort order in report


Doris,
sorry but no success :(
My report looks like:
1. Mike Thompson

    1. plum 155

2. Ervin Brindza

    1. orange 100

3. Ervin Brindza 

   1. apple 80

4. Mike Thompson

         1. orange 40

even I put the Customer name in the SumOfAmount's header or in
Customer.Name's header the result is the same :(
Thanks for your input
    Ervin

----- Original Message ----- 
From: Mike and  <mailto:mikedorism at ntelos.net> Doris Manning 
To: 'Access Developers discussion and  <mailto:accessd at databaseadvisors.com>
problem solving' 
Sent: 03 September, 2003 1:32 PM
Subject: RE: [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

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



  _____  




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


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