Question Help with connecting forms/tables etc? :/

[PinInPastels]

It's for my case study, and I'm stuck at this question. They want us to design the database for the company.

The question is, "The database is used for stock control. When an order is entered, the database will indicate if any item has fallen below a set stock level. Design & make a system which will perform this task. (Print a single A4 page which shows any functions, formulae, macros, extra fields or other features you have used. + "Annotate the page to explain how the system works." and "Print a screen showing how the database indicates that an item's stock level is too low.")

I have a table for orders, a table for customers and a table for items. Let's say the customer is buying an apple, and the set stock level (on the items table) is 10. I have set a validation rule that alerts me if the user enters less than 10 into the field. However, I do not know how to make the stock level decrease every time I enter an order. How can I do this, and how can I be alerted if the level does go below the set stock level?

Thank you in advance, I really need help.

 

[llkhoutx]

I've worked on inventory systems that reduced inventory by orders on various events. Decide which event requires you to reduce inventory and run a query which decrements inventory by your order.