Add subtract query

[rythem]

I have a table called the inventory table with a field called stock quantity.
i want to be able to have a consolidated quantity for all the stock with the same Serial number and not have different entries every time i input a quantity.

Also, i want to be able to subract from this consolidated quantity the amount that is removed by anyone and the result displayed in another table called check stock

can anyone help

thanks

 

[KeithIT]

i want to be able to have a consolidated quantity for all the stock with the same Serial number and not have different entries every time i input a quantity.

Just have an unbound field on your form for "quantity" and set the on update property of that field to an event procedure that adds the value of that field to the value in the table for the quantity of that item, then returns the new value to the table in place of the old one (not sure if my wording is confusing here or not...)

How adept are you at VB? If you can't do this, post again here and I or someone will help you with the code you'll need for this procedure.

i want to be able to subract from this consolidated quantity the amount that is removed by anyone and the result displayed in another table called check stock

Same deal here. In fact, you can use the same field in the table, just have another unbound field on your "quantity removed" form (or whatever you are calling it) and have the event procedure for the on_update property of that field subtract the value in the unbound field from the quantity in the table, and then update the table again with the new value.

To have this display on another form, just have the other form's field bound to the column in the table that contains the quantity. As long as your code is working properly the table will always be up to date and the quantitity displayed on any form will be accurate.

If this doesn't help or you need more don't hesitate to ask!

 

[neileg]

Sorry, but your design is wrong. You should not store the balance, you should store all the individual entries and sum them to calculate the balance any time you need them. If you search this forum you will find many postings on inventory/stock. Some of them will explain why your approach is wrong.

 

[KeithIT]

Neil,
Thanks for your advice. I searched as suggested and your answer now makes a whole lot more sense. I don't think I could have come up with all the problems that result from storing a calculated value unless I got to the point where I needed to create a database that utilized this information. Thanks again for the help!