Trouble with three firlds

[Hubbymax]

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

 

[Al Campagna]

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

The current inventory count of P/N 1234-11 = DSum of Rcvd - DSum of Disb
(10 - 2 = 8)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[George]

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]

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






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

 

[Rudolf Lamour]

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

 

[Rudolf Lamour]

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

 

[Rudolf Lamour]

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

 

[Al Campagna]

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

Are you up to that point with your table design?
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

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






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