what do i design so that the database warns me to order more stock?

[xtrareal22]

i need serious help.i am a beginner using access 2007.I have 3 tables.<br>INVENTORY<br>SALES<br>SUPPLIER<br>I have created a an update query that deducts the qty amount after a transaction is made and update that value to the qty field in the INVENTORY table.Now i want to the database to warn me when the stock(inventory) level starts to run low.How in the world do i do this without having to use VBA?

[June7]

Build a report and run the report every day.

However, the design where you save calculated data is usually a bad idea. Review http://allenbrowne.com/AppInventory.html

The example shows use of VBA but not necessarily required. A series of queries could probably get the desired data manipulation.

[rpeare]

I can not agree with june7 more, you do NOT want to update your tables every time you add/remove a stock item. What you really want is a table that stores your products (product table), a table that stores your receipts or dusbursements (purchase orders from your customers) then calculate your on hand value based on how much of the item you have received vs how much you have disbursed. Your product table can contain a 'warning threshhold' so that if your 'on hand' calculation falls below a certain level it can be triggered for either a report as June7 suggests, or on a form.

Updating your table with a new calculation every time you make a change is a very bad idea. Just think of it this way.

You come in to work tomorrow and suddenly you have zero stock (according to the database) of everything. If you don't have the original record of when you received and spent items you have no way of auditing your system to find out what went wrong nor would you be able to tell a customer when they purchased a certain item from you and in what quantities based on the DATA and not having to track down a piece of paper.

[orange]

Further to what June7 and rpeare have said, in inventory systems there is often a ReOrder Level associated with a Product. Not all Products have the same ReOrder level. After having disbursed/sold some Product, and recording that disbursement in a transaction table, you can query your transaction table for the Quantity of that Product OnHand. You can compare the Quantity of Product OnHand with the ReOrder Level for that Product, and take appropriate action.
In some DBMS you can create a "trigger" - code that automatically executes based on some condition - and program it such that an email or order is sent to the Supplier requesting the ReOrder Quantity for that Product whose Quantity OnHand has reached or fallen below the ReOrder Level.

Inventory and Stock Control systems can become quite complex.

Good luck with your project.

[xtrareal22]

i understand how to do it.thanks for the help