Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    Totals query divided per months (Access 2000)

    I have a query consiting of 2 columns : afid and SumOfLiters. The sql view of the query is the following:

    SELECT customers.afid, Sum([order details].liters) AS SumOfliters
    FROM (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
    GROUP BY customers.afid;

    In the above query i view all the quantities sold up to now for each afid. I want to view them divided per month.
    Can somebody help me build a query showing the quantities sold per each month?
    There was a way with Format ?
    Also, how shall i declare them to follow not the name of the month but one by another, e.g. first to be january and second february?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Totals query divided per months (Access 2000)

    I don't think you need the Products table in there - it contributes nothing.

    You haven't mentioned what the date field is. Let's say you have a field OrderDate om which you want to group by month. Add two expressions to the query grid (in this order!)


    OrderMonth:Format([OrderDate],"mmmm yyyy")

    Leave the Totals option for both as Group By, but clear tha Show check box for the first one. This means that it will be used for grouping and sorting, but not displayed; the second expression displays the date in a nice format.

Posting Permissions

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