sequential minus (1 Viewer)

papa john

Registered User.
Local time
Today, 23:26
Joined
Nov 19, 2019
Messages
17
Hi,

For below example table: -

orderproductquantitydate
1a301/01/1111
2a202/01/1111
3b101/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.

thanks in advance
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:26
Joined
Jul 9, 2003
Messages
16,245
Are you saying, You want to use the oldest stock first?
 

papa john

Registered User.
Local time
Today, 23:26
Joined
Nov 19, 2019
Messages
17
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

productqty
a6

orders

orderproductquantitydate due
1a3day1
2a4day2
3a3day3

need to make

orderproductquanitiydate due
1a0day1
2a1day2
3a3day3
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:26
Joined
Sep 21, 2011
Messages
14,048
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.
Local time
Today, 23:26
Joined
Nov 19, 2019
Messages
17
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
Local time
Today, 23:26
Joined
Sep 21, 2011
Messages
14,048
I was thinking of a running sum query?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:26
Joined
Feb 28, 2001
Messages
27,001
Glad you mentioned it, Gasman.

@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
Local time
Today, 18:26
Joined
May 11, 2011
Messages
11,613
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!
Local time
Tomorrow, 07:26
Joined
May 7, 2009
Messages
19,169
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.
Local time
Today, 23:26
Joined
Nov 19, 2019
Messages
17
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
Local time
Today, 18:26
Joined
May 11, 2011
Messages
11,613
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
Local time
Today, 19:26
Joined
Feb 19, 2002
Messages
42,979
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.
 

Users who are viewing this thread

Top Bottom