Query to update qty on insert in another table

[Daniel]

I have a site im working on and certain details on the page are based on the
qty we show to have available..

How can i write a query or insert so that when a new record is added to our
order table the product table reduces the qty of the item by 1?


And since most of the orders have to be manually verified for payment.. we
dont want to decrease the number until the payment has been approved..

Does this make sense?

The site is a yearly thing.. once the items are sold in a month or so, then
they wont be selling anything until maybe next year..

Currently the DB is access and just looking to see what our options are for
this..

 

[bcap]

That sounds like a de-normalised design. Sometimes this can be appropriate,
but you should certainly consider the normalised approach of, instead of
storing the outstanding quantity on the product table, simply calculating it
in a query whenever you need to know it.

In principle, your outstanding quantity is simply the opening quantity less
the quantity of verified paid orders. This would normally be a pretty
simple query, although I can't say that for certain without knowing your
database design.