Thread: Total sum (Access 2000)

1. 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. Re: Total sum (Access 2000)

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

3. 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. 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. 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
•