inventory level adjustment

T

Todd Roche

How would I make it so the level of my inventory would automaticly adjust the
amount.? If I enter a date in the orders table as the order is completed the
inventory level for the item would subtract one.
 
S

Steve

It doesn't sound like your tables are correct. They should be:

TblProduct
ProductID
ProductName
ProductPrice
ProductInventory

TblCustomer
CustomerID
etc.

TblOrder
OrderID
CustomerID
OrderDate
etc.

TblOrderLineItem
OrderLineItemID
OrderID
ProductID
Quantity
ProductPrice

You should have a form/subform for entering orders. The main form based on
TblOrder and the subform based on TblLineItem. When you enter Quantity in
the subform, code in the Afterupdate event of Quantity should adjust
ProductID's ProductInventory in TblProduct by Quantity.

Steve
(e-mail address removed)
 
G

George Hepworth

Actually, what you are proposing is not a sound approach to inventory
management in this environment.

This is a fairly broad and complex subject, so you might benefit from some
background reading on managing inventory in a database, as well as table
design, but basically, here's how it should be handled.

The Inventory table has a field for the product being tracked, e.g.
ProductID.
The Inventory table has a field for "AdjustmentAmount", in which all
additions to and subtractions from inventory are entered as positive or
negative values.
The Inventory table has a field indicating the reason for the adjustment,
e.g. "Shipment Received", or "Sale Fulfilled".
The Inventory table has a field for the date on which the adjustment is
made, e.g. "TransactionDate"

At any given point, "StockOnHand" is a calculated value and is the result of
summing all "AdjustmentAmounts" in a query by ProductID.

In most systems, there will be periodic reconciliations between the
inventory calculated in the database and an actual count of items on hand in
the warehouse. An adjusting amount is added to the inventory table to bring
it into alignment with actual stock on hand on that date.

As noted, inventory management can be quite complex, so it would be good to
do some additional research before committing to a table design for your
application.


George
 
G

George Hepworth

Not really, Steve.

Professional database developers suggest that good design is NOT to store
calculated values like that.

George
 
G

George Hepworth

There is another way this can be handled, which is equally valid, but
perhaps more appropriate if you are working with order fulfillment.

In that case, you would actually have two tables, one for inventory, which
stores records of additions and stock-take adjustments, and one for the
detail lines of orders, in which you'd store the quantities of products sent
out to fulfill orders.

In this scenario, your current inventory is calculated in a set of queries.
The first sums all Adjustments in the Inventory table, including
stock-takes, and the second sums all order quantities in the order detail
table. A third query combines these two source tables, and does a simple
subtraction to get the current stock on hand amount.

Again, professional database designers will not store calculated values
because it can easily lead to loss of data integrity.
 
S

Steve

What calculated value are you referring to?


George Hepworth said:
Not really, Steve.

Professional database developers suggest that good design is NOT to store
calculated values like that.

George
 
G

GP George

" When you enter Quantity"

When you "adjust" something, that is a result of a "calculation".
 
G

GP George

I think I'm just going to put a link to his site in my sig line and be done
with it. LOL
 
D

De Jager

Todd Roche said:
How would I make it so the level of my inventory would automaticly adjust
the
amount.? If I enter a date in the orders table as the order is completed
the
inventory level for the item would subtract one.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2007 0
Inventory Help 3
Creating Multi Level list using Header Style 1
Inventory/Sales/Warehouses 6
Search in combo box 3
Lookup Box 8
Couple subs need help with 2
Inventory Transaction Problem 1

Top