Need Help with Creating a Database

[Gunnu]

Hello,
I am trying to create a database using access. I want to store the
current inventory of boxes and other moving supplies. When i sell an
item from this inventory to a customer, i want to store that
transaction and reduce that item in the inventory. How can i do this?
I am confused in setting up the relationships between the tables. I
know that i would need these table:
(1). Customer table
(2). Order table
(3). Product table
But how do i represent, for example, " A customer purchases many
products in one order" and how do i decrease those items quantity in
the product table.
Thanks,
Bharath

 

[Ken Sheridan]

Bharath:

The Order table should include a foreign key column, e.g. CustomerID,
referencing the primary key of the Customer table. You then need an
additional table, OrderDetails say, to model the many-to-many relationship
between The Order table and the Product table. This would have two foreign
key columns, e.g. OrderID and ProductID, referencing the primary keys of
those tables, and a Quantity column for the number of each product ordered in
the order, along with other columns as necessary. You'll find a similar set
of tables in the sample Northwind database which comes with Access.
Northwind does not automatically compute current inventory however.

For returning the current stock-in-hand figure for each product you can
compute this on the fly by subtracting the sum of the values in the Quantity
column for each product from the sum of items added to stock. For items
added to stock you could have a StockReceived table with a foreign key
ProductID column, a DateReceived column and a Quantity column, or you could
simply include an OpeningStock column in the Product table.

If you have a StockReceived table then the current stock-in-hand per
product could be returned with a query such as:

SELECT ProductID, ProductName,
(SELECT SUM(Quantity)
FROM StockReceived
WHERE StockReceived.ProductID = ProductProductID)
-(SELECT SUM(Quantity)
FROM OrderDetails
WHERE OrderDetails.ProductID = ProductProductID)
AS StockInHand
FROM Product;

If you simply record the opening stock in a column in Product it would be:

SELECT ProductID, ProductName,
OpeningStock
-(SELECT SUM(Quantity)
FROM OrderDetails
WHERE OrderDetails.ProductID = ProductProductID)
AS StockInHand
FROM Product;

You'll need to include some way of writing off stock which is removed from
stock but not supplied to a customer, e.g. damaged or outdated stock. This
could be done as a dummy order for a phantom customer named "WrittenOff", by
having a separate table for write-offs, or by entering a negative quantity in
the StockReceived table.

Ken Sheridan
Stafford, England