Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report in descending order (Access 2000)

    <P ID="edit" class=small>(Edited by HansV on 13-Oct-03 11:37. Removed superfluous line breaks)</P>I need some help to build a report enumerating the products in descending order depending on the sum of liters. In this way i can view the top products sold for a given period.But i fail to do it. In my query i have build a total as follows

    SELECT products.Productid, products.grade, products.size, Sum([order details].liters) AS SumOfliters, orders.invoicedate
    FROM products INNER JOIN ((affiliates INNER JOIN Customers ON affiliates.afid = Customers.afid) INNER JOIN (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) ON Customers.Customerid = orders.customerid) ON products.Productid = [order details].ProductID
    GROUP BY products.Productid, products.grade, products.size, orders.invoicedate
    ORDER BY Sum([order details].liters) DESC;

    In this query i do not have the whole quantity of each product, but i do not want to make use of two queries. I have succeeeded to build a query enumerating each product with the total quantity for this product, but this quantity is not in descending order, beging with the biggest sum of liters. In the Sorting and grouping optioins of the report i have :
    productid ascending

    Adding "sumofliters" in this option does not change the order or the products shown. Can somebody help me build a report showing the sums of liters for each product in descending order?

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

    Re: Report in descending order (Access 2000)

    1. If you create a query for use as the record source of a report, you shouldn't specify a sort order in the query. Access reports completely ignore the sort order of the record source; all sorting is done through the Sorting and Grouping window. Specifying a sort order in the query will slow things down.

    There is one exception: you can specify that the query should return only the top 5 (or top 10, or any number) records. In that case, you do need to specify the sort order.

    2. If you want to sort on SumOfLiters (descending), this must be the first entry in the Sorting and Grouping window. If you put it below ProductID, you can select the entire row containing SumOfLiters and drag it to the top position.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report in descending order (Access 2000)

    Thank you so much for your detailed reply. I know that the fault is mine.I didnt explain in full what i need.
    I still cannot make my report working.In the Sorting and Grouping window
    i have put:
    SumOfLiters - descending
    ProductID - ascending

    The report so obtained give me a nice view for the totals of all products. However, it does not list the products according to the the total quantity
    sold. This quantity is not SumOfLiters,but the sum of it.to be found in the ProductID footer and is :
    =Sum([SumOfliters])

    Therefore, what i want is to arrange my products in descending order
    not according their SumOfLiters, but according to their =Sum([SumOfliters])
    which is in the report footer and giving summarized quantity of the each product.
    Is it possible to put that =Sum([SumOfliters]) in the Sorting and Grouping window?.
    I have tried, but it says that i have an extra bracket which is not true

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

    Re: Report in descending order (Access 2000)

    In the first post in this thread, you wrote that you do not want to use a second query. But I fear that you are trying to do too much in one step now. You will need to create a totals query that calculates the "total total" for each product. You can use this to specify the sort order of your report.

Posting Permissions

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