query per month (Access 2000)

Topic

New Reply

I have a query that orders the customers in ascending order depending on the liters sold.How can i order the customers also per month.? For example if customer A is on the first place with the highest liters, i want to see his liters orderded also per month and the months also ordered in ascending order, staring from january.Also, how can i write January instead of 1 ?
My working query is the following :
SELECT customers.CompanyName, Sum([order details].liters) AS SumOfliters, customers.Customerid, affiliates.afid
FROM affiliates INNER JOIN ((customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID) ON affiliates.afid = customers.afid
WHERE (((Month([invoicedate]))>1) AND ((Year([invoicedate]))=2007) AND ((orders.paymentid)=True))
GROUP BY customers.CompanyName, customers.Customerid, affiliates.afid
HAVING (((affiliates.afid)=1))
ORDER BY Sum([order details].liters) DESC;

Reply | Quote

Replies

If you want to sort on the overall sum of the liters and also return the number of liters per month, you’ll have to create a new query that groups the records by customer and by month,
Then create a third query that combines the two queries, joined on the customerid field. You can use the MonthName function to display the name of the month.

Alternatively, create a crosstab query with customer as row header, month as column header and sum of liters as value field.

Reply | Quote

Thank you for your advice.I have built the crosstab query giving me months for each customer, but Accesd does not allow me to sort the customers from the field liters in descending order.Can i improve on that ?

TRANSFORM Sum([order details].liters) AS SumOfliters
SELECT customers.CompanyName, customers.Customerid
FROM affiliates INNER JOIN ((customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID) ON affiliates.afid = customers.afid
WHERE (((orders.paymentid)=True) AND ((affiliates.afid)=1) AND ((Year([invoicedate]))=2007))
GROUP BY customers.CompanyName, customers.Customerid
PIVOT Month([invoicedate]);

Reply | Quote

You should add Sum([order details].[liters]) AS TotalLiters to the SELECT part of the query, so that you get a total number of liters.

Then create a new query based on the crosstab query and sort it descending on TotalLiters.

Reply | Quote

Yes, it all comes up the way i wanted.Thank you so much !!!

Reply | Quote