inventory level adjustment

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.
--
Todd

I'm using access 2003
--
Todd


"Todd Roche" wrote:

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.
--
Todd

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




"Todd Roche" wrote in message
...
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.
--
Todd

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

"Todd Roche" wrote in message
...
I'm using access 2003
--
Todd


"Todd Roche" wrote:

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.
--
Todd

Not really, Steve.

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

George

"Steve" wrote in message
...
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




"Todd Roche" wrote in message
...
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.
--
Todd

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.



"George Hepworth" wrote in message
news
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

"Todd Roche" wrote in message
...
I'm using access 2003
--
Todd


"Todd Roche" wrote:

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.
--
Todd

Todd,

Have a look at...

http://allenbrowne.com/AppInventory.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Todd Roche" wrote in message
...
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.
--
Todd

What calculated value are you referring to?


"George Hepworth" wrote in message
...
Not really, Steve.

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

George

"Steve" wrote in message
...
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




"Todd Roche" wrote in message
...
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.
--
Todd

" When you enter Quantity
in the subform, code in the Afterupdate event of Quantity should adjust
ProductID's ProductInventory in TblProduct by Quantity.
"

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


"Steve" wrote in message
...
What calculated value are you referring to?


"George Hepworth" wrote in message
...
Not really, Steve.

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

George

"Steve" wrote in message
...
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




"Todd Roche" wrote in message
...
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.
--
Todd

Thanks, Gina. Allen covers everything doesn't he?

"Gina Whipp" wrote in message
...
Todd,

Have a look at...

http://allenbrowne.com/AppInventory.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Todd Roche" wrote in message
...
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.
--
Todd