Updating Stock Quantity

[jordanturner]

Hi all,



I am making a small database for a small electronics company local to me.

I am basically trying to update the quantity in stock field in the stock table when a group of items have been dispatched.
I have a dispatch form with the fields:

DispatchID
EmployeeID
Location
Dispatch Date

This form has a sub form named frmDispatchLine with the fields:
DispatchLineID
DispatchID
StockID
QuantityDispatched
QuantityReturned

I was thinking that the code for the query would go behind the save button on the form but I have searched for a long time on the web and cannot find the code I need. Any help would be greatly appreciated. Thanks

[pbaldy]

Generally speaking it's better to calculate quantity on hand from transactions than to try to keep it updated. Here's a discussion of the topic, with thoughts on how to do what you want to do at the bottom:

http://allenbrowne.com/AppInventory.html

[jordanturner]

Thanks for the reply Pbaldy and I have looked at the article a number of times. I even started to create the database in that way and ran into a few problems. I then asked my university tutor who teaches databases about it and she said it would be fine to store a quantity on hand value. I thought this strange myself but she just said that if I use update queries it will be ok. Also a few of the database templates i have looked at also have quantity stored as a value so i am assuming this way will work fine being that it is only a relatively small business

[pbaldy]

Does she have real-world experience? The smaller the business, the less likely I'd be to store the value. I'd consider it when a huge number of transactions were involved. I've written inventory apps and I have yet to store quantity on hand. That all said, it's your baby, so you should do what you feel most comfortable with. You need to account for every possible way a user could change the value, as detailed on Allen's site.

[jordanturner]

I'm not really sure if she does. I would assume so as she has a masters in database design. The real problem I was having when I was originally calculating the fields the right way was the purchase order. I had a sub form for each line of the purchase order. The fields were:
TransactionID, TransactionTypeID, ItemName, QuantityOrdered, UnitPrice, QuantityReceived. The problem was that if a purchase order was made, it would have a transaction type "Order". The user would then come back to the purchase order when the order arrived and then fill in the received coloumn. My problem here was that it would be a different type of transaction, a "Delivery" or "Received" on the same transaction as an "Order". So maybe I was not handling the goods received part of the db in the right way but I couldn't think of any way around it. The user needed to see the original purchase order to fill in the received part from a delivery. So this problem and the fact that a tutor has told me it should be ok to store this value just made me believe It would be fine, but the more I look into it the more I believe I should change my design and do things the correct way.
I hope this has made sense and any pointers in the right direction on how to handle deliveries from the purchase order would be greatly appreciated.
Thanks again

Jordan T

[pbaldy]

From the sound of it, for purposes of calculating quantity on hand you would only care about the received column. You ignore the ordered column. Since I assume all of your incoming items would be handled the same way, you'd sum up the received column on "Order" transaction types.

[RuralGuy]

I'm not really sure if she does. I would assume so as she has a masters in database design...
Jordan T

IMHO...the higher the degree, the longer in school and the less time to actually get any practical experience.

[jordanturner]

Thanks for the reply Pbaldy. You will probably be glad to here that I have redesigned that db in the correct way and you are right, the quantity ordered can be ignored and the calculation will look at the quantity received column. The report works fine now for generating the quantity on hand but just need a couple of pointers on the stock take side of things and how to handle them as I know over time it will take longer and longer to calculate the QOH. I think a stock take table is needed but not really sure how to a stock will be done.
Thanks again guys

Regards
Jordan T

[RuralGuy]

I think a stock take table is needed but not really sure how to a stock will be done.
Thanks again guys

Regards
Jordan T

You will notice in the link PBaldy provided that Allen has a "tblStockTake" in the design. The process to complete the table is itterative and separate from the normal system. You would walk through the inventory one product at a time calculating the current inventory for each item and filling in the "tblStockTake" as you go. I've not done it but it seems to me that it could be done each time you request a current inventory of an item because the process will be the same.

[Mclaren]

I am a noob in essence to ms access and VB, i have self taught for the most part and gained an increadible amount of info from these pages and some other forums.

I can tell you i have been faffing with this for about 5 years or so and still have yet to successfully create a stock control DB. There is always some new problem i come accross. I can say i have managed to use Allens example, heavily modified now, but i have made it work. My code is very buggy though, so all i can say is GOOD LUCK. in most instances the best solution is to spend some money on an already made solution, there are a few good brands out there.

I spent about $500 on a decent package that did most of what i required.

I have not yet though stopped working on my version.

[ajetrumpet]

IMHO...the higher the degree, the longer in school and the less time to actually get any practical experience.

Allen,

IMO, Technology requires a lot more experience than education. Value is with the experience, not in the classroom. Accounting seems to be a little bit different though - you learn the laws, then apply them to the work.

[oldman]

The way I am reading it, you have a stock quantity of X amount that was prespecified in your last inventory. The problem becomes a simple one of always adding together all of the draws from stock and the returned number plus those added by bringing in new stock. If you are getting accurate numbers for those things, the remaining stock is simply (the number found in stock at inventory) - (the number issued) + (the number returned) + (the number added from new orders).
In theory, if the numbers are well maintained, you can get all of the routine stock manipulations other than new purchases from the table that underlies frmDispatchLine.