Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    order by in crosstab query (Access 2000)

    I want to order by my crosstab query depending on liters, ordering them in descending way, when the customers with the biggest liters come first. Can you help me add it on the query which is :

    PARAMETERS [Forms]![frmQuarterSales]![Office] Long;
    TRANSFORM Val(Nz(Sum([order details].[liters]),0)) AS SumOfLiters
    SELECT customers.Customerid, customers.CompanyName, customers.city
    FROM (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    WHERE (((customers.afid)=[Forms]![frmQuarterSales]![Office]) AND ((orders.paymentid)=True))
    GROUP BY customers.Customerid, customers.CompanyName, customers.city
    PIVOT Format([InvoiceDate],"q");

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: order by in crosstab query (Access 2000)

    You cannot sort the query you now have by the sum of liters, since it is the value field of the crosstab query. You could add the overall sum of liters (over all quarters) per customer as a row field, and sort on that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •