I have receivables form that has 3 fields I am having trouble with.
UnitsInStock AddUnits TotalUnits
UnitsInStock is drawn from a Products table, AddUnits is put in by the user, TotalUnits is a sum of the first two fields. I would like to have the UnitsInStock updated by the TotalUnits field once the sum for the TotalUnits is done and the AddUnits field returned to "0".
Hubbymax, Think of a parts inventory as a checkbook. Money comes in and money goees out. Your inventory table should be like a checkbook... able to handle both Credits and Debits... PartNo Rcvd Disb 1234-11 3 0 17265-4 6 0 1234-11 0 2 1234-11 4 0 4153-33 10 0 1234-11 3 0
>I have receivables form that has 3 fields I am having trouble with.
> UnitsInStock > AddUnits > TotalUnits
> UnitsInStock is drawn from a Products table, AddUnits is put in by > the > user, TotalUnits is a sum of the first two fields. I would like to > have the UnitsInStock updated by the TotalUnits field once the sum > for > the TotalUnits is done and the AddUnits field returned to "0".
There is a better way. What you are doing is storing a calculated field, which is generally considered a bad design in any table. You should not be risking loss of integrity in your data by calculating and recalculting amounts.
Inventory management can be one of the more complex applications to manage, but the essential issue here is that you need only Two inventory adjustment fields, not three. Those fields are "AddUnits" and "AddReason", or more probably a different set of field names better suited to the requirement, such as "Adjustment" and "AdjustmentReason". That allows you to track both positive changes (Inventory received from vendors) and negative changes (shrinkage).
When units are added to, or subtracted from stock, you insert a record into AddUnits and an ID to indicate the reason for the adjustment. The SUM of all such additions is your UnitsInStock before adjustments for sales, adjustments, etc. Sales records are added, I assume, in a separate sales transaction table, and the same process can be applied to it.
The basic query to return that would be:
"Select Product, SUM(Adjustment) AS TotalAdjustments FROM tblInventory GROUP BY Product"
To account for sales, of course, you need something similar:
"Select Product, SUM(QuantitySold) AS TotalSales FROM tblSaleDetails GROUP BY Product"
To get your current Units in Stock, you combine the two, subtracting total sales from total adjustments:
Select Product, SUM(tblInventory.Adjustment) - SUM(tblSaleDetail.QuantitySold) as UnitsInStock FROM tblInventory LEFT JOIN tblSaleDetails ON tblInventory.ProductID = tblSaleDetail.ProductID GROUP BY Product
Whereever you need the current Units in Stock amount, you get it from this query.
I am aware that there are other approaches to inventory tracking, and that the exact implementation depends to a certain extent on how your business operates. Nonetheless, this is the basic approach you need to take.
George
"Hubbymax" <jaredal...@hotmail.com> wrote in message
>I have receivables form that has 3 fields I am having trouble with.
> UnitsInStock > AddUnits > TotalUnits
> UnitsInStock is drawn from a Products table, AddUnits is put in by > the > user, TotalUnits is a sum of the first two fields. I would like to > have the UnitsInStock updated by the TotalUnits field once the sum > for > the TotalUnits is done and the AddUnits field returned to "0".
> There is a better way. What you are doing is storing a calculated field, > which is generally considered a bad design in any table. You should not be > risking loss of integrity in your data by calculating and recalculting > amounts.
> Inventory management can be one of the more complex applications to manage, > but the essential issue here is that you need only Two inventory adjustment > fields, not three. Those fields are "AddUnits" and "AddReason", or more > probably a different set of field names better suited to the requirement, > such as "Adjustment" and "AdjustmentReason". That allows you to track both > positive changes (Inventory received from vendors) and negative changes > (shrinkage).
> When units are added to, or subtracted from stock, you insert a record into > AddUnits and an ID to indicate the reason for the adjustment. The SUM of all > such additions is your UnitsInStock before adjustments for sales, > adjustments, etc. Sales records are added, I assume, in a separate sales > transaction table, and the same process can be applied to it.
> The basic query to return that would be:
> "Select Product, SUM(Adjustment) AS TotalAdjustments FROM tblInventory > GROUP BY Product"
> To account for sales, of course, you need something similar:
> "Select Product, SUM(QuantitySold) AS TotalSales FROM tblSaleDetails > GROUP BY Product"
> To get your current Units in Stock, you combine the two, subtracting total > sales from total adjustments:
> Select Product, SUM(tblInventory.Adjustment) - > SUM(tblSaleDetail.QuantitySold) as UnitsInStock > FROM tblInventory LEFT JOIN tblSaleDetails ON tblInventory.ProductID = > tblSaleDetail.ProductID > GROUP BY Product
> Whereever you need the current Units in Stock amount, you get it from this > query.
> I am aware that there are other approaches to inventory tracking, and that > the exact implementation depends to a certain extent on how your business > operates. Nonetheless, this is the basic approach you need to take.
> George
> "Hubbymax" <jaredal...@hotmail.com> wrote in message
> >I have receivables form that has 3 fields I am having trouble with.
> > UnitsInStock > > AddUnits > > TotalUnits
> > UnitsInStock is drawn from a Products table, AddUnits is put in by > > the > > user, TotalUnits is a sum of the first two fields. I would like to > > have the UnitsInStock updated by the TotalUnits field once the sum > > for > > the TotalUnits is done and the AddUnits field returned to "0".- Hide quoted text -
> - Show quoted text -
The more I get into this the more I find myself way beyond my current abilities. My program is for internal transactions within a company. Shipping and Receiving is the only "purchaser" from outside recourses and there are numerous divisions that "purchase" from Shipping and Receiving. The only info entered from the outside recourses is the amount received of a product and it's cost. All I wanted to do was have the TotalUnits field update the UnitsInStock. I was hoping an "After Update", "on Click", "On Exit" or some other command that would take place after the calculation of the TotalUnits field would do the trick. If this query code is the only way to go I will give it a try.
>I have receivables form that has 3 fields I am having trouble with.
> UnitsInStock > AddUnits > TotalUnits
> UnitsInStock is drawn from a Products table, AddUnits is put in by > the > user, TotalUnits is a sum of the first two fields. I would like to > have the UnitsInStock updated by the TotalUnits field once the sum > for > the TotalUnits is done and the AddUnits field returned to "0".
>I have receivables form that has 3 fields I am having trouble with.
> UnitsInStock > AddUnits > TotalUnits
> UnitsInStock is drawn from a Products table, AddUnits is put in by > the > user, TotalUnits is a sum of the first two fields. I would like to > have the UnitsInStock updated by the TotalUnits field once the sum > for > the TotalUnits is done and the AddUnits field returned to "0".
>I have receivables form that has 3 fields I am having trouble with.
> UnitsInStock > AddUnits > TotalUnits
> UnitsInStock is drawn from a Products table, AddUnits is put in by > the > user, TotalUnits is a sum of the first two fields. I would like to > have the UnitsInStock updated by the TotalUnits field once the sum > for > the TotalUnits is done and the AddUnits field returned to "0".
Hubbymax, Whenever you deal with an inventory system, the first thing is to get the tables right. One table named tblInventory can contain the fields necessary to handle both receiving to inventory as well as disbursing from inventory. Your checkbook is a good example of an "inventory" table... each line entry can be a Credit or a Debit. Ex... Key PartNo Desc Price Rcvd Disb 152X-1 Flange 1.33 10 17RD-9 Bar .74 5 152X-1 Flange 1.33 7
> There is a better way. What you are doing is storing a calculated field, > which is generally considered a bad design in any table. You should not be > risking loss of integrity in your data by calculating and recalculting > amounts.
> Inventory management can be one of the more complex applications to > manage, > but the essential issue here is that you need only Two inventory > adjustment > fields, not three. Those fields are "AddUnits" and "AddReason", or more > probably a different set of field names better suited to the requirement, > such as "Adjustment" and "AdjustmentReason". That allows you to track both > positive changes (Inventory received from vendors) and negative changes > (shrinkage).
> When units are added to, or subtracted from stock, you insert a record > into > AddUnits and an ID to indicate the reason for the adjustment. The SUM of > all > such additions is your UnitsInStock before adjustments for sales, > adjustments, etc. Sales records are added, I assume, in a separate sales > transaction table, and the same process can be applied to it.
> The basic query to return that would be:
> "Select Product, SUM(Adjustment) AS TotalAdjustments FROM tblInventory > GROUP BY Product"
> To account for sales, of course, you need something similar:
> "Select Product, SUM(QuantitySold) AS TotalSales FROM tblSaleDetails > GROUP BY Product"
> To get your current Units in Stock, you combine the two, subtracting total > sales from total adjustments:
> Select Product, SUM(tblInventory.Adjustment) - > SUM(tblSaleDetail.QuantitySold) as UnitsInStock > FROM tblInventory LEFT JOIN tblSaleDetails ON tblInventory.ProductID = > tblSaleDetail.ProductID > GROUP BY Product
> Whereever you need the current Units in Stock amount, you get it from this > query.
> I am aware that there are other approaches to inventory tracking, and that > the exact implementation depends to a certain extent on how your business > operates. Nonetheless, this is the basic approach you need to take.
> George
> "Hubbymax" <jaredal...@hotmail.com> wrote in message
> >I have receivables form that has 3 fields I am having trouble with.
> > UnitsInStock > > AddUnits > > TotalUnits
> > UnitsInStock is drawn from a Products table, AddUnits is put in by > > the > > user, TotalUnits is a sum of the first two fields. I would like to > > have the UnitsInStock updated by the TotalUnits field once the sum > > for > > the TotalUnits is done and the AddUnits field returned to "0".- Hide > > quoted text -
> - Show quoted text -
The more I get into this the more I find myself way beyond my current abilities. My program is for internal transactions within a company. Shipping and Receiving is the only "purchaser" from outside recourses and there are numerous divisions that "purchase" from Shipping and Receiving. The only info entered from the outside recourses is the amount received of a product and it's cost. All I wanted to do was have the TotalUnits field update the UnitsInStock. I was hoping an "After Update", "on Click", "On Exit" or some other command that would take place after the calculation of the TotalUnits field would do the trick. If this query code is the only way to go I will give it a try.
|
