Report showing products in descening order

[Hohn Upshew]

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?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you want the liters sorted within the ProductID grouping? If so in
the report's Sort/Group By dialog box put the ProductID field above
the SumOfLiters field and set the SumOfLiters order Descending. This
will give a result like this:

ProductID Grade Size SumOfLiters
- --------------------------------------
1 a 2" 250
1 z 3" 100
1 x 1" 50
3 t 10" 400
3 c 2" 38
25 a 3" 1000
25 z 11" 60

If you don't care about the sort order of the ProductID then put the
SumOfLiters field above the ProductID field in the Sort/Group By
dialog box. This will give a result like this:

ProductID Grade Size SumOfLiters
- --------------------------------------
3 a 5" 250
5 z 10" 100
1 x 1" 50

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4rrt4echKqOuFEgEQKPfgCeI0Y+C/kM+O8owIXxNm0vvoY1mwMAn0jO
k4ctfd7fKGP3grcrMNRt5Xmj
=/GRX
-----END PGP SIGNATURE-----

Hohn Upshew wrote:

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?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry Linson]

I had a little difficulty determining just how you were trying to accomplish
arranging the data in descending order.

Grouping and Ordering in reports is, indeed, controlled by the Sorting and
Grouping properties you have specified. If the "Sumofliters" field in your
query is the first item in Sorting and Grouping, and you have selected
Descending order, then that's the way your Report should be arranged.

If you have some other field in the Sorting and Grouping list before
Sumofliters, then, of course, Sumoflitiers would not be the highest level
sort, but only a sort within a higher-level sorting or grouping.

Larry Linson
Microsoft Access MVP
"Hohn Upshew" <fa*****@yahoo.com> wrote in message
news:3f*********************@news.frii.net...

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?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!