GUIDO22
Registered User.
- Local time
- Today, 09:12
- Joined
- Nov 2, 2003
- Messages
- 515
I have a STOCK table which holds unique stock records for materials held in our system- I also have a form on which I can select items from the STOCK table that I wish to 'pick'. Any picked items are added to a temporary table STOCK_TEMP.
Initially the picking form is populated with a query that retrieves data from the STOCK table to determine current quantity levels.
However, once I have 'picked' certain stock items when I look at another contract to allocate stock to - I need to take into account stock that has already been picked.
So I have two SQL queries :
One that returns a table containing the actual STOCK quantities
AND another query that returns a similar format STOCK table but the quantity field in this case is the picked quantity
I need to run a third query that essentially combines the results from these other two... such that these results returned represent the quantities remaining that are now available for subsequent picks.
(you may well be wondering why I have done it this way : I cannot be sure our actual physical stock matches that held on the database, so I dont want to update the master STOCK record as soon as the stock is picked, because if it transpires the stock doesnt exist or cannot be physically taken from our material stores - rolling back afterwards could be even more of a headache)
Thanks in advance for any help/advice.
Initially the picking form is populated with a query that retrieves data from the STOCK table to determine current quantity levels.
However, once I have 'picked' certain stock items when I look at another contract to allocate stock to - I need to take into account stock that has already been picked.
So I have two SQL queries :
One that returns a table containing the actual STOCK quantities
AND another query that returns a similar format STOCK table but the quantity field in this case is the picked quantity
I need to run a third query that essentially combines the results from these other two... such that these results returned represent the quantities remaining that are now available for subsequent picks.
(you may well be wondering why I have done it this way : I cannot be sure our actual physical stock matches that held on the database, so I dont want to update the master STOCK record as soon as the stock is picked, because if it transpires the stock doesnt exist or cannot be physically taken from our material stores - rolling back afterwards could be even more of a headache)
Thanks in advance for any help/advice.