Running Totals in a Query 1

[UberZoot]

Hello Again!!
I am hoping that someone can give a hand. I am trying to create a query that will pull info from a "Scheduling" table and a "Production" table. I've gotten this far. I wanted to have the difference calculated between the set scheduled production and the multiple inputs of actual production so that a running difference would be calculated. What I have achieved thus far is all save the running difference. Instead, I get the scheduled production placed against the multiple actual production entries, but the scheduled production is constantly refreshed to its original amount. For example, I schedule 1400 blue wheels on 9/12/03. I produced 500 on 9/13 and 500 on 9/14. My relationships are set to match the dates, product, and amounts. But I get 900 remaining on 9/12 and 900 remaining on 9/13. How can I have the totaled scheduled production to update dynamically as production is entered and achieve the running difference? I sincerely appreciate any and all help. Thanks.

 

[Golom]

I would need to guess about too many things to give an answer. Can you post some sample data showing the input tables that you have and the result that you want to see?

 

[UberZoot]

No problem Golom.

Orders Due Table
Date Product Quantity (on order placed)
9/11/03 Wheels 5000
9/12/03 Chairs 400

Production Table
Date Product Quantity (produced)
9/11/03 Wheels 2500
9/13/03 Wheels 1500
9/13/03 Chairs 400

I have the query set up to match up the orders to the production (based on product (direct) and production date being >= order date. I have a field that calculates the diff between the order quantity for that product and the produced quantity for that product. I need the base order total to update as production is entered against it in multiple entries. I also want to include an expression that will set the order quantity as &quot;Complete&quot; once the order quantity reaches <=0. I hope this helps. Thanks again in advance.

 

[Golom]

OK. Here it is in one query (or at least, here is my interpretation of it.)

SELECT DISTINCT O.Product,

(SELECT Sum(SO.Quantity) FROM Orders As SO Where SO.Product = O.Product) As [Ordered],

(SELECT Sum(SP.Quantity) FROM Production As SP
Where SP.Product = O.Product AND SP.ProductionDate >= O.OrderDate) As [Produced],

IIF ( ((SELECT Sum(SO.Quantity) FROM Orders As SO Where SO.Product = O.Product) -
(SELECT Sum(SP.Quantity) FROM Production As SP
Where SP.Product = O.Product AND SP.ProductionDate >= O.OrderDate)) > 0,

((SELECT Sum(SO.Quantity) FROM Orders As SO Where SO.Product = O.Product) -
(SELECT Sum(SP.Quantity) FROM Production As SP
Where SP.Product = O.Product AND SP.ProductionDate >= O.OrderDate)),&quot;COMPLETE&quot As [Remaining Order]

FROM Orders As O INNER JOIN Production As P ON O.Product = P.Product

ORDER BY O.Product


Since this is so gaudy ... here's a bit of a breakdown of what's happening.

(SELECT Sum(SO.Quantity) FROM Orders As SO Where SO.Product = O.Product) As [Ordered]
Sums the Quantity Ordered of a product.


(SELECT Sum(SP.Quantity) FROM Production As SP
Where SP.Product = O.Product AND SP.ProductionDate >= O.OrderDate
Sums the quantity produced.


iif ( Difference>0, Difference, &quot;COMPLETE&quot
Tests if the difference between ordered and produced is greater than zero. If it is then it sets the query field &quot;Remaining Order&quot; to the difference. If it is <= 0 then it sets the field to &quot;COMPLETE&quot;.

Using your sample data, this gives the following result

Product	Ordered	Produced	Remaining Order

Chairs      400          400            COMPLETE
Wheels      5000         4000           1000

There is some concern about performance with these extensive sub-queries. Each product in this sample causes six sub queries to be run in addition to the main query. You should insure that the &quot;Product&quot; fields and the Date fields in each table are indexed to improve performance as much as possible.

 

[Golom]

Oops! The last FROM should be

FROM Orders As O

 

[UberZoot]

Golom,
Thanks for the help. I am still a novice in this program, and was wondering where I would need to insert this code. Thanks again.

 

[UberZoot]

Thank you thank you thank you. That is the answer I needed. Is there any way that I can have any record that hits &quot;Complete&quot; delete automatically? Thanks again.

 

[Golom]

You can't do a delete from this query but you could set up a separate delete query. The question is, what do you want to delete? The Orders?, The Production? Both?

 

[UberZoot]

I'd like to delete all information associated with the completed order. For now, I've conditionally formatted a form based onthe query to highlight these fields in green when the order is complete. Thanks again & again.