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

    Total sum (Access 2000)

    I have a list box made out of the following query:
    SELECT Customers.Customerid, Sum([order details].liters) AS SumOfliters
    FROM (Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    GROUP BY Customers.Customerid;
    This query enumerates the sum of the liters for each order.I would like to have the figure for the total liters in the form for each customer. Can i calculate the total liters on the form above the list box ? The form is not based on tall he tables in the query.It is based only on the table customers.

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

    Re: Total sum (Access 2000)

    I don't understand. The SQL provides total liters per customer, not per order.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total sum (Access 2000)

    Yes it i true. I have to show the whole query. the first row, the number of the customer is hidden.Then the number of the payment is shown and then the liters for this order.The whole query is the folloiwng:

    SELECT Customers.Customerid, orders.paymentid, Sum([order details].liters) AS SumOfliters
    FROM (Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    WHERE (((Customers.house)=1) AND ((Customers.MyOffice)=False) AND ((orders.paymentid)>0))
    GROUP BY Customers.Customerid, orders.paymentid, orders.orderid;

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

    Re: Total sum (Access 2000)

    Create a query with the SQL from your first post:

    SELECT Customers.Customerid, Sum([order details].liters) AS SumOfliters
    FROM (Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    GROUP BY Customers.Customerid;

    Save this query as qryLitersPerCustomer.
    Put a text box on your form with the following control source:
    <code>
    =DLookup("SumOfLiters","qryLitersPerCustomer","Cus tomerID=" & [CustomerID])
    </code>
    This text box will display total liters for the current customer.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total sum (Access 2000)

    Super !!! Thank you. I am so much obliged to you

Posting Permissions

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