inventory level adjustment
in [General]
|
Prev: car sale
Next: Problems installing Office 2007
From: Todd Roche on 10 Mar 2010 19:30 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
From: Todd Roche on 10 Mar 2010 19:31 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
From: Steve on 10 Mar 2010 19:40 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 santus(a)penn.com "Todd Roche" <ToddRoche(a)discussions.microsoft.com> wrote in message news:B5000573-3D0B-41A1-817D-9B7A592F374B(a)microsoft.com... > 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
From: George Hepworth on 10 Mar 2010 19:45 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" <ToddRoche(a)discussions.microsoft.com> wrote in message news:C914AB54-DA02-4844-A1F8-C290C4C54E09(a)microsoft.com... > 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
From: George Hepworth on 10 Mar 2010 19:48
Not really, Steve. Professional database developers suggest that good design is NOT to store calculated values like that. George "Steve" <notmyemail(a)address.com> wrote in message news:upsi9NLwKHA.5132(a)TK2MSFTNGP05.phx.gbl... > 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 > santus(a)penn.com > > > > "Todd Roche" <ToddRoche(a)discussions.microsoft.com> wrote in message > news:B5000573-3D0B-41A1-817D-9B7A592F374B(a)microsoft.com... >> 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 > > |