Inventory Help Please!!

[lookup13]

I have a good Question , I have two tables (1)inventory
(2)Orders
I have relationships with part# , So when I run A Query
Inventory & Order I only see what is used .
See Example
Date Part# Rel orderAmount Inventory Stock
8/22/03 1234 1 10 12 2
8/25/03 1234 2 2 12 10

However the problems occurs if same part# is used ,How
can I make this work. (rel--is short for release--kepts track # times item has been ordered)
Is there way to add as a group then subtract Inventory
I would like to run a report that would show up to date
Stock.
Any And All Ideal Welcomed
Thanks

 

[willir]

lookup13

Typically, an inventory system is very similar to transaction system.

Product table
- document product name, specifications, etc, BUT not quantity

Customer table / Supplier table
- can be two or one table

Order table
- Documents orders, used to generate the invoice, etc

Inventory table
- Documents transtion of inventory, in and out

PLUS typical G/L, A/P and A/R tables; maybe shipping as well.

The key is that inventory is treated as transaction - receipts = add inventory, order + ship = reduction.

Although not part of normalization, the Product or similar table will keep a current balance of inventory. You can get the same number by running a SUM SQL statement on the Inventory table.

There are some really exceptional books on inveotry systems.

Hope this helps. Kids to feed.

Richard

 

[Gazonice]

I have written several stock control systems:

I use one table to hold details of the stock, another related table to track each instance an item is put into stock, and another related table that tracks each instance an item is sold.

You can use a query to calculate remaining stock by deducting items sold from the third table, from the items added in the second table.

This method works well and you can use detailed information about when stock is added or sold (eg. date/time added into stock, or who by or where etc etc). You can also keep detailed information about when an item is sold, who to, by whom etc etc.

Hope this helps,

Garry