Want stock to update in stock table when order is made in order ta

[Saj]

Hi anyone

Can anyone help me with the following:

I have a table called stock which has a field for stock called
'stockQuantity' which basically holds the value of how much stock is
available.
When a customer places an order I have an order form with subform orderline,
in the subform I enter the stockno. then the 'quantity' they want.
I want the table with the stock to update on what is ordered, so if customer
orders e.g 3 pairs of shoes and 5 were in stock, the stock table will update
by decrementing the stock level to 2.

May be a simple query for someone out there, but I cant think at the moment
and need some urgent help.... thanks in advance.

 

[Tom Ellison]

Dear Saj:

May I please offer some advice. Don't do it!

The quantity on hand can be calculated. Keeping a calculated value stored
in a table means every little thing that could affect that total must also
update this total for the change it makes. I believe you will find that
doing this will be very daunting.

Consider, for example, what you must do if the customer reports a shortage
in a shipment. If you believe him, then you must change the quantity
shipped. This means you would have to increase the quantity on hand. But,
if it was lost or damaged in shipment you would not.

When I write inventory, I have "starting points" for quantity on hand.
Whenever a certain stock item is counted in any warehouse, this count is
entered in a table of transactions to inventory.

Various queries to tables of receipts and shipments of inventory create
uniform "inventory transaction records". These are added up and summed with
the most recent count to find the quantity on hand as of any date. This
works without strain or failure to account for all changes.

Trying to keep this in a table is a violation of principles for good
database construction, principly because it is a lot of programming to make
sure everything is summed into this one total. If your system has, or ever
comes to have multiple locations for the inventory, this becomes even more
important.

Tom Ellison