If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
inventory level adjustment
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 |
#3
|
|||
|
|||
inventory level adjustment
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 |
#4
|
|||
|
|||
inventory level adjustment
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 |
#5
|
|||
|
|||
inventory level adjustment
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 |
#6
|
|||
|
|||
inventory level adjustment
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 |
#7
|
|||
|
|||
inventory level adjustment
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 |
#8
|
|||
|
|||
inventory level adjustment
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 |
#9
|
|||
|
|||
inventory level adjustment
" 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 |
#10
|
|||
|
|||
inventory level adjustment
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 |
|
Thread Tools | |
Display Modes | |
|
|