Stock Control

A common type of project is the stock control database, and the main problem in this type of system is keeping the stock levels up-to-date. You could use update queries, and one of the more popular A level textbooks proposes a method using VBA.

Both of these methods can lead to an integrity nightmare - you only need your function, query or macro to fail and your stock levels will be wrong - you won't have any idea how many items you have in stock.

I would question whether you need to store the current stock levels at all - they could be calculated from the number of items delivered and sold. Assuming that you start with an empty warehouse, the number of any item in stock will be the number delivered minus the number sold. If you don't start with no stock, or you need to adjust the stock levels, you could simply create dummy suppliers or customers and adjust them using deliveries or sales.

How it works in detail

To calculate the number of items in stock, it's safest to use three queries - one to total the deliveries, one to total the sales, and one to subtract sales from deliveries. The first two are very similar - shown below is the query shown in the downloadable database to count the deliveries:

The query uses the Sum() function to group and total the deliveries for each item. The purpose of the generated deliveries column is to use iif() to replace null values (i.e. blanks) with zeros, as you can't subtract from a blank.

Note also the arrow on the relationship - this indicates that the type of the relationship has been changed so that all records from tbl_stock are included (the middle option). This ensures that all stock items are included in the query results, rather than just the ones for which there are deliveries.

A similar query is created to total the sales, and then a third query is created:

This query is based on the first two queries - you will need to link the two "tables" yourself. A formula is then used to subtract the number of items sold from the number delivered, to give the current stock level.

The stock database can be downloaded as an .mdb or .accdb file. .