inventory level adjustment
in [General]
|
Prev: car sale
Next: Problems installing Office 2007
From: George Hepworth on 10 Mar 2010 19:54 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" <george_hepworth(a)hotmail.com> wrote in message news:D59C7EFE-7173-4546-8DCD-75FD29CF087A(a)microsoft.com... > 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: Gina Whipp on 10 Mar 2010 19:57 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" <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: Steve on 10 Mar 2010 19:57 What calculated value are you referring to? "George Hepworth" <george_hepworth(a)hotmail.com> wrote in message news:O%232NNSLwKHA.6140(a)TK2MSFTNGP05.phx.gbl... > 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 >> >>
From: GP George on 10 Mar 2010 20:31 " 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" <notmyemail(a)address.com> wrote in message news:uXZigXLwKHA.3408(a)TK2MSFTNGP06.phx.gbl... > What calculated value are you referring to? > > > "George Hepworth" <george_hepworth(a)hotmail.com> wrote in message > news:O%232NNSLwKHA.6140(a)TK2MSFTNGP05.phx.gbl... >> 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 >>> >>> > >
From: GP George on 10 Mar 2010 20:33
Thanks, Gina. Allen covers everything doesn't he? "Gina Whipp" <NotInterested(a)InViruses.com> wrote in message news:OVtPSXLwKHA.5132(a)TK2MSFTNGP05.phx.gbl... > 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" <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 > |