Northwind Database

[Lynn]

I need to create a small database to keep track of some parts. I can easily
strip down the Northwind database and have exactly what I need. One of the
most important things I need to be able to see is how many parts are on hand
at a given time. In looking at the Northwind databse that feature does not
seem to be functional. If I place and order, then go look at the quantity on
hand the number does not change (in the table or reports). Is there an
updated Northwind database with this function operational? Or can someone
help me with this. I took a college Access class about 5 years ago and my
skills are very rusty. Any help would be greatly appreciated.

 

[Ken Sheridan]

The Northwind database only deals with sales orders not purchase orders, so
the current stock in hand per product is simply a value in the UnitsInStock
field in the Products table, which would have to be maintained manually as
stock is purchased/sold. To automatically compute the current stock in hand
per product it would be necessary to include tables for purchase orders which
mirror the current ones for sales orders. The stock in hand could then be
computed with a query which subtracts the values returned by two subqueries,
one to purchase orders, one for sales orders, e.g.

SELECT ProductName,
(SELECT SUM(Quantity)
FROM [Purhase Order Details]
WHERE [Purchase Order Details].ProductID = Products.ProductID) -
(SELECT SUM(Quantity)
FROM [Sales Order Details]
WHERE [Sales Order Details].ProductID = Products.ProductID) AS StockInHand
FROM Products;

This is itself something of an oversimplification, however, as it assumes
all items ordered in the Purchase Order Details table have been received in
good order. It also takes no account of stock written off or of goods
returned into stock by customers So in a real life business environment
you'd need to amplify the subqueries to take these factors into account. I
hope, however, that the above helps point you in the right direction.

Ken Sheridan
Stafford, England