Solved SQL Union/Join type query...

[GUIDO22]

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.

 

[plog]

So, you've got a table that doesn't necessarily reflect reality, but you're afraid to make changes to this possible incorrect table because it might make it incorrecter? That doesn't make sense. This sounds like you are just asking what type of duct tape and super glue to use in fixing your fundamentally unsound system.

I understand a database not reflecting reality, but I don't understand breaking normalization because somehow this mitigates things. All stock credits and debits should be in one table. If you can have different types of stock transactions (supply, sale, spoilage, returns, pending, etc.) you add a field to designate the type of that transaction. Then, because all credits and debits are on one table you are able to determine stock levels (whether they reflect reality or not).

Can you post a copy of your database?

 

[GUIDO22]

I hear what you are saying , essentially you are right. Nevertheless the stock quantities cannot be 100% relied upon so i figured this would be a reasonable way of ensuring some means of recovery in the event that i issue stock that in all reality doesn't exist ...
I am not fixing an unsound system.

People are people and sometimes take from stock without following the correct procedure of requisitioning stock and updating the database accordingly.
Not a lot i can do about that .. short of CCTV and firing the offending individuals.

My database is normalised but this a rather unusual scenario that i have been asked to implement a solution for.

The simple solution is sometimes difficult to see when you have gone too far down the rabbit hole.. thanks for the solution, will give it a try tomorrow.

 

[CJ_London]

I wouldn't use a separate temp table, all stock transactions should be in one table - just include a flag for 'picked' which can then be converted to 'dispatched'/whatever when that event happens. In the meantime you can ignore picked records or not as the case may be.

edit: just realised much the same as Plog's advice

 

[GUIDO22]

Thanks for the reply CJ and Pat.. as i had already to alluded in my response to Plog, i am doing the single table solution that of course makes the most sense.. i was too blinkered yesterday with everything going on and all , to see it for myself.