Report in descending order (Access 2000)

Topic

New Reply

(Edited by HansV on 13-Oct-03 11:37. Removed superfluous line breaks)

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote