Stock Control System

[Buckaroo]

Hi I am building a Database for a local Minimart and I am currently focussing
on its Stock Control System.
There is a quantity for each product. Now when an employee puts through the
products a customer is buying, I want the quantity of each of those products
to be reduced by 1. This means that the Quantity of the product will
continually be updated when a product is bought, which enables the manager to
know exactly how much stock of each item is left.
At the moment I have a form for a new Transaction, which includes a Subform
which lists the products the customer is buying.
Is there anyway of doing this?
Thanks

 

[Ken Sheridan]

Your subform will presumably be based on a table such as SaleDetails which
includes columns such as ProductID ( a foreign key referencing a Products
table) and Quantity (the number of items of the product sold in the sale.

I would anticipate that you also have, or intend to have as you develop the
application, tables for recording stock acquired e.g. a Purchases table with
a related PurchaseDetails table to record each product purchased from a
supplier in each purchase transaction. This would be very similar in
structure to the SaleDetails table, having a ProductID column ( again a
foreign key referencing a Products table) and Quantity (the number of items
of the product purchased in the transaction).

Consequently you do not need to store the quantity in stock for each product
as this can be computed at any time with a query, always giving an up-to-date
quantity in stock per product:

SELECT PurchaseDetails.ProductID,
SUM(PurchaseDetails.Quantity) – NZ(SUM(SaleDetails.Quantity),0) AS
QuantityInStock
FROM Purchase.Details LEFT JOIN SaleDetails
ON PurchaseDetails.ProductID = SaleDetails.ProductID
GROUP BY PurchaseDetails.ProductID;

Note that you need to use an outer join here and to use the NZ function to
cater for products which have been taken into stock, but of which no sale has
yet been made.

You will also need to take account of stock written off and of items
returned into stock. The former can be recorded simply as another 'sale'
transaction, but with some means of indicating that it is a write-off.
Conversely the latter can be recorded as another 'purchase' transaction, once
again indicating that the purchase is actually a return-to-stock.

Ken Sheridan
Stafford, England

 

[Buckaroo]

It took some fiddling but it worked in the end.
Thanks a lot!

 

[Dorg]

hi there

I read your post and was hoping and praying but will you be able to send me
that template. its exactly what i am looking for?

Thanks in advanced.
Ryan