Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query per month (Access 2000)

    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;

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

    Re: query per month (Access 2000)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query per month (Access 2000)

    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]);

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

    Re: query per month (Access 2000)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query per month (Access 2000)

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

Posting Permissions

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