Monitoring Inventory Transactions

[Lord_Icon]

Hi,

I am new in using Access07 and I am currently doing a program for an
internal inventory. I will try to be as clear as possible with my problem.

First, I have 4 tables:
1. Supply Table (ProductID, ProductName, CurrentStock, CategoryID)
2. InventoryTransaction (IT_ID, IT_Date, IT_Received) - Product received
3. InventoryConsumption (IC_ID, IT_Date, IT_Request) - Product consumed

Here is my dilemma, a product could have multiple transactions received and
consuemd. What i want to track is my CurrentStock. Whenever I receive a
delivery for a specific product, I need to add that to the CurrentStock
balance and whenever a customer request for a product, that is deducted to my
CurrentStock. So basically, I need to have an accurate tracking of my actual
balance for my products.

Any advice is highly appreciated.

Thank you,

 

[kc-mass]

You need to put the ProductID in tables 2 and 3 as a foreign key that lets
you link the transactions back to the product. You also need a numeric
quantity in tables 2 and 3 for how much you received or consumed (Maybe your
IT_Received and IT_Consumed??). You don't need and shouldn't have the
CurrentStock field in table 1. Once you make the changes to the tables you
can link the three together and get reliable quantities any time you want
with a totals query.

Regards

Kevin

 

[Tom van Stiphout]

On Tue, 24 Nov 2009 21:25:02 -0800, Lord_Icon

Tracking inventory is such a basic requirement you won't have any
problem searching the web for examples.
I personally don't like the idea of having a CurrentStock field.
Rather I would use a StockTake table, and then calculate the current
level based on last record in StockTake + additions - removals.

-Tom.
Microsoft Access MVP

 

[Lars Olofsson]

Hi,

I am new in using Access07 and I am currently doing a program for an
internal inventory. I will try to be as clear as possible with my problem.

First, I have 4 tables:
1. Supply Table (ProductID, ProductName, CurrentStock, CategoryID)
2. InventoryTransaction (IT_ID, IT_Date, IT_Received) - Product received
3. InventoryConsumption (IC_ID, IT_Date, IT_Request) - Product consumed

Here is my dilemma, a product could have multiple transactions received
and
consuemd. What i want to track is my CurrentStock. Whenever I receive a
delivery for a specific product, I need to add that to the CurrentStock
balance and whenever a customer request for a product, that is deducted to
my
CurrentStock. So basically, I need to have an accurate tracking of my
actual
balance for my products.

Any advice is highly appreciated.

Thank you,