Solved SQL Union/Join type query... (1 Viewer)

[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

 

[Pat Hartman]

You are not solving a problem, you are creating worse one. Plog's advice is sound.

If the picker goes to a bin and there is insufficient stock, the picker needs to have a way to fix the problem. Perhaps, he should be able to add a "shrinkage" transaction that adjusts inventory down to the actual level. Then you have to be able to fix the picked transaction and adjust it also. That leaves a third issue which relates to the order. What are you going to do about that? Are you going to do a partial shipment? I don't know what your business rules are so I can't tell you what to do.

The point that plog was making as well as CJ and myself is that ALL inventory transactions belong in one single table. There are a number of different types of transactions. All of them explain what action was taken and some will add to the quantity on hand and others will subtract from it.

Putting some transactions in a different table doesn't uncomplicate the issue, it complicates it and it has a ripple effect.

Dealing with inventory is a little like dealing with money. You don't ever update a record to change a quantity. You always add a new transaction to add or subtract. Then all the transactions are summed to get the quantity on hand or balance in your checking account. EVERY adjustment has a meaning and creates an audit trail. You may never determine how the items went "missing" from the bin but the correction transaction explains that you had to fix it because what the quantity said was not reality.

PS, to use the two tables, which I strongly suggest you don't, you would use a left join. of two queries. one that sums the inventory and one that sums the picked table. You would then subtract the picked qty from the inventory qty. Keep in mind that the picked qty could be null if there are no pending transactions in that table so you would use Nz()
Net:tblInventory.Qty - Nz(qrySum.Qty)

 

[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.