# sequential minus (1 Viewer)

#### papa john

##### Registered User.
Hi,

For below example table: -

 order product quantity date 1 a 3 01/01/1111 2 a 2 02/01/1111 3 b 1 01/01/1111

say you had 4 units of product "a" in stock. how could you minus the stock sequentially from each record until it had been used up? so for example you minus 4 from the quantity of order 1, then order 1 would then have a qty of 0, or the record would be removed, then the remaining 1 stock would then be substracted from order 2, leaving order 2 with 1 quantity remaining.

Basically i want to know how much i need to make of each product on each day, but if i already have it in stock, i know i wont need to make some of the orders so grouping by product and then subtracting isnt a option.

#### Uncle Gizmo

##### Nifty Access Guy
Staff member
Are you saying, You want to use the oldest stock first?

#### papa john

##### Registered User.
Are you saying, You want to use the oldest stock first?
I'm not worried about the age of the stock (we don't record it). I want to see what what items i need to make from our orders. so if i have 3 orders on for item "A" totaling 10 in quantity and i have 6 of item "A" in stock, i know i only need to make 4 lots of item "A". but if the first order due is for 3, then i know i dont need to make any for this order becasue i already have 6 in stock, but if the next order is for 4, then i only have 3 left in stock after the first order so i only need to make 1, and id need to make the full amount for the third order because i dont have any stock left for it.

below are tables to illustrate the above example

stock table

 product qty a 6

orders

 order product quantity date due 1 a 3 day1 2 a 4 day2 3 a 3 day3

need to make

 order product quanitiy date due 1 a 0 day1 2 a 1 day2 3 a 3 day3

#### Gasman

##### Enthusiastic Amateur
I am confused. You know how many you need, you know how many you have, the difference being the number you need to make if orders are more than stock?

#### papa john

##### Registered User.
I am confused. You know how many you need, you know how many you have, the difference being the number you need to make if orders are more than stock?
3 orders
order "1" is for 3
order "2" is for 4
order "3" is for 3

you have 6 in stock

order "1" you take 3 from stock - you have 3 stock left
order "2" you take the remaining 3 from stock, you have to make 1 - you have 0 stock left
order "3" you have no stock left, you have to make all 3

in summary and what i ultimately want to know

order "1" need to make 0
order "2" need to make 1
order "3" need to make 3

#### Gasman

##### Enthusiastic Amateur
You still make 4?

#### papa john

##### Registered User.
You still make 4?
yes you need to make four, but you can't simply group it together because i need to know when they are due

Staff member

#### theDBguy

##### I’m here to help
Staff member
Me three, but I have a feeling (or hope) that this may come in handy.

#### Gasman

##### Enthusiastic Amateur
I was thinking of a running sum query?

#### The_Doc_Man

##### Immoderate Moderator
Staff member

@papa john - the USUAL way you do an open-ended inventory is you do not have a table of what you have. You have a table of transactions. You work the signs so that you add inventory or subtract it by summing the transactions. For instance, you create a new thing? That becomes a stock add. You sell something? That is a stock remove. How many things do this? Your call. But the idea is that every transaction has a "transaction type" that can be translated as stock add or stock remove. Then do a sum of all transactions. Rather obviously, you have to do stock adds before you can sell anything, but ALL repeat ALL items start with stock balance zero and then work from there.

Your method is going a different way so I can tell you that down the road you will get more complex things that will vex you as you try to bang on this to "make it fit."

For the moment, given the problem you stated, you can't do more than one transaction at a time, which pretty much takes about half of SQL out of the picture. The problem as stated will require VBA and programmed recordset operations to do what you want.

#### plog

##### Banishment Pending
To quote Morty: This is just a running total with extra steps. I did this with 2 query objects where the first one uses a correlated subquery to do the running total, then the second query object does the math in determining the quantity for each order:

Code:
``````SELECT orders_main.order, orders_main.product, orders_main.quantity, orders_main.datedue, (SELECT SUM(quantity) FROM orders WHERE product=orders_main.product AND datedue<=orders_main.datedue)-[qty] AS deficit
FROM orders AS orders_main INNER JOIN stock ON orders_main.product = stock.product
ORDER BY orders_main.order;``````

Paste that into a query object and name it "q_sub1". Then use the below query to get the results you want:

Code:
``````SELECT q_sub1.order, q_sub1.product, q_sub1.quantity, IIf([deficit]<=0,0,IIf([deficit]>[quantity],[quantity],[deficit])) AS needed
FROM q_sub1;``````

#### arnelgp

##### ..forever waiting... waiting for jellybean!
the OP need another field in his table.
if the order can be satisfied, then a field, "served" can be set to true.
in this case, only those not "served" are included in the query.

without such field, imagine your query is still returning old
orders with "0" when you only need is those not yet served,
5 months from now (or forever?).

#### papa john

##### Registered User.
To quote Morty: This is just a running total with extra steps. I did this with 2 query objects where the first one uses a correlated subquery to do the running total, then the second query object does the math in determining the quantity for each order:

Code:
``````SELECT orders_main.order, orders_main.product, orders_main.quantity, orders_main.datedue, (SELECT SUM(quantity) FROM orders WHERE product=orders_main.product AND datedue<=orders_main.datedue)-[qty] AS deficit
FROM orders AS orders_main INNER JOIN stock ON orders_main.product = stock.product
ORDER BY orders_main.order;``````

Paste that into a query object and name it "q_sub1". Then use the below query to get the results you want:

Code:
``````SELECT q_sub1.order, q_sub1.product, q_sub1.quantity, IIf([deficit]<=0,0,IIf([deficit]>[quantity],[quantity],[deficit])) AS needed
FROM q_sub1;``````

you sir are a gentleman and a scholar, through my own research on this problem i was looking at sub queries / running totals. sub queries being a novel concept to my self, the solution wasn't immediately obvious.

your first query gave some errors, i had to do some further research on the topic of sub queries to make it work, i have also made some refinements, such as changing the join type so it didn't discard orders that didn't have any stock.

Code:
``````SELECT orders_main.order, orders_main.product, orders_main.quantity, orders_main.datedue, (select sum(a.quantity) from orders_main as a where a.product = orders_main.product and a.datedue <= orders_main.datedue)-[stock].[quantity] AS deficit
FROM orders_main LEFT JOIN stock ON orders_main.product = stock.product
ORDER BY orders_main.product, orders_main.datedue;``````

your second query functioned without errors, but again discarded orders without stock, please see below for my refinement: -

Code:
``````SELECT q_sub1.order, q_sub1.product, q_sub1.quantity, q_sub1.datedue, IIf([deficit] Is Null,[quantity],IIf([deficit]<=0,0,IIf([deficit]>[quantity],[quantity],[deficit]))) AS needed
FROM q_sub1
ORDER BY q_sub1.product, q_sub1.datedue;``````

Last edited:

#### plog

##### Banishment Pending
Glad you got it working. You should give arnelgp's post some thought though. You need a way to identify which orders are "complete" such that they don't effect the query results--perhaps by duedate. You shouldn't update, delete or move records, you should have a way to use the data itself to identify records to exclude from these queries.

#### Pat Hartman

##### Super Moderator
Staff member
Poppa John,
You have defined a convoluted process that I have never performed in all the dozens of inventory applications I have developed. It is important to separate all the concepts you are combining, mostly because they happen at different times and so would never be performed with a running sum especially since that type of query doesn't update the table anyway and at the end of the process you need to know how many you have left in inventory and be able to mark all the orders that were fulfilled.

Start with figuring out how many of anything you need to make. To do that you need to know what you have in stock (simple assuming you have only one record per part for inventory. Start by creating a query of unfulfilled orders/items and summarize the quantity needed by part number. At this point it makes no difference which order needs the part. Test the query. Back in design view, add the inventory table and join to it on part number. select the on hand quantity. Add a criteria statement below the sum of the required quantity

> tblInventory.QtyOnHand

Voila. YOu have a query with part number, required quantity, quantity on hand and it only contains rows where the required quantity exceedes the on hand quantity. To make it a little more user friendly add another calculation that subtracts OnHand from Required so you know how many of each part must be made.

It is of course possible to run a separate query for each product but why you would want to do this is beyond me. Just produce ONE required parts output report that goes to your manufacturing operation and let them handle the individual processes.

Once you get that done, we can work on fulfilling the orders and depleting the inventory as it is allocated to an order.

BTW. I think it is best to use an inventory transaction table rather than to just update the inventory record since simply updating the inventory record leaves no audit trail and that will ultimately make your life a living hell. But we don't know your schema or anything else we need to know to really help.

Replies
13
Views
471
Replies
5
Views
276
Replies
3
Views
252
Replies
5
Views
396
Replies
9
Views
181