Calculated Inventory Field With Historu

[pokeytrev]

Thanks to another user who helped me with a previous topic, I have created a database wherein exists an inventory table (to list the current inventory level) and a transaction table (to keep track of additions to inventory and depletions as line items). How would I go about linking the inventory additions and depletions to the current inventory quantity?

For example, I want to receive 3 of item A and add it to the existing Item A inventory of 2 making it a current level of 5.

Essentially I want to keep track of current inventory levels as well as a running list of transactions.

Edit:
And yes, I spelled "History" wrong. Sorry about that.

 

[plog]

Sounds like your database is improperly structured. You wouldn't store the inventory value, you would simply calculate it from all your transactions:

InventoryTransactions
TransID, TransDate, Item, TransQuantity
1, 1/1/2016, A, 5
2, 1/3/2016, A, -4
3, 1/5/2016, A, 7
4, 1/6/2016, A, -4
5, 1/7/2016, A, -2
6, 1/8/2016, A, 3

That's all you need to tell you the current inventory for A is 5.

 

[arnelgp]

you can also store the inventory on the table. then you can compare this with your transaction audit.

imagine if this is a bank and you only want to get your account balance, when you have opened your account since 1970. and every day you have deposit, withdrawal, it would take long time for this to calculate.

 

[pokeytrev]

you can also store the inventory on the table. then you can compare this with your transaction audit.

imagine if this is a bank and you only want to get your account balance, when you have opened your account since 1970. and every day you have deposit, withdrawal, it would take long time for this to calculate.

Yes, that is exactly what I want to do. I want to have inventory levels in one table and the transactions in another table. Any tips on how I could go about doing this?

 

[arnelgp]

always use form to enter your transaction. and use query not table.
the query should link your transaction table to the inventory table by product code, ie:

select * from tblTransaction, tblProducts.InvQty Left Join tblProducts On tblTransaction.ProdCode = tblProducts.ProdCode

it can be a one form with tab control for adding (receipt) and issuance (withdrawal). or if you elect one form with negative value of qty for issuance.

on the after_update of your qty control:

private sub qty_afterupdate()
dim rs as dao.recordset
set rs=me.recorsetclone
rs!InvQty = Nz(rs!InvQty.Value, 0) + nz(Me.qty.Value) - Nz(qty.OldValue,0)
set rs=nothing
end sub

 

[pokeytrev]

I will give this a shot. Thank you very much

 

[MarkK]

You need a product table that identifies what you are counting. Then each transaction has fields like . . .

tTransaction
TransactionID (Primary Key)
ProductID (Foreign Key)
Quantity
Date

Then, stock level is not stored in a table, it is calculated in a query as follows . . .

SELECT Sum(Quantity) As Balance
FROM tTransaction
WHERE [Date] <= #[I]<DateOfBalance>[/I]# 
   AND ProductID = [I]<ProductIDToCount>[/I]

 

[pokeytrev]

always use form to enter your transaction. and use query not table.
the query should link your transaction table to the inventory table by product code, ie:

select * from tblTransaction, tblProducts.InvQty Left Join tblProducts On tblTransaction.ProdCode = tblProducts.ProdCode

it can be a one form with tab control for adding (receipt) and issuance (withdrawal). or if you elect one form with negative value of qty for issuance.

on the after_update of your qty control:

private sub qty_afterupdate()
dim rs as dao.recordset
set rs=me.recorsetclone
rs!InvQty = Nz(rs!InvQty.Value, 0) + nz(Me.qty.Value) - Nz(qty.OldValue,0)
set rs=nothing
end sub

I am having trouble getting the form to run correctly. Take a look at my relationships and tell me what you think I may need to do?

TblTransaction
*
Transaction ID KEY
Product Code
Description
Transaction Qty

TblProducts
*
Product Code KEY
Part Description
Vendor ID
InvQty

tbltransaction.transactionid it joined to tblproducts.invqty

 

[MarkK]

I am having trouble getting the form to run correctly.

Can you be more specfic about these symptoms?

 

[pokeytrev]

Can you be more specfic about these symptoms?

I tried the method arnelgp posted, and noticed that the query that combined tblTransaction and tblProducts was frozen, meaning I couldn't edit it. I was just wondering if the relationships were wrong based on this:

TblTransaction
*
Transaction ID KEY
Product Code
Description
Transaction Qty

TblProducts
*
Product Code KEY
Part Description
Vendor ID
InvQty

tbltransaction.transactionid it joined to tblproducts.invqty

 

[MarkK]

I don't think you need a query (or a form) that joins Transaction and Product. Do you?

But to enter a transaction you'll want to specify the Product involved, and to do so you need to edit the ProductID field in tblTransaction. So on the transaction form, put a ComboBox on the ProductID field, and look up the products that way, not with a fully joined query.

And tblTransaction and tblProduct would normally be joined on ProductID. Also, your life will be easier too if you don't use spaces in names, so change "Transaction Qty" to TransactionQty, or, since the table is called Transaction, just "Qty."

All the best,

 

[pokeytrev]

I don't think you need a query (or a form) that joins Transaction and Product. Do you?

But to enter a transaction you'll want to specify the Product involved, and to do so you need to edit the ProductID field in tblTransaction. So on the transaction form, put a ComboBox on the ProductID field, and look up the products that way, not with a fully joined query.

And tblTransaction and tblProduct would normally be joined on ProductID. Also, your life will be easier too if you don't use spaces in names, so change "Transaction Qty" to TransactionQty, or, since the table is called Transaction, just "Qty."

All the best,

Good tips! I don't really know if they need to be connected, like you said. I am going to try a different variety of things to see what works best and then possible seek further help from there.

Thanks

 

[arnelgp]

see the code behind in Form1

 

[Gasman]

see the code behind in Form1

arnelgp,

When I select a date via the date picker on that form, it does not put any date into the field.? Would you be able to tell me why please.?

 

[pokeytrev]

see the code behind in Form1

This is very helpful. Thank you.

I will try fiddling around with it a bit to see if I can figure out how it works.

 

[arnelgp]

it does really puy the date when you actually type something in the subform.
the idea is:
1. on new record we update the invqty with our transaction qty (plus or minus).
2. if you decide you made wrong entry and you want to delete that record, it updates the inventory quantity. reversing the process when you create the record the first time.
if you add, it will deduct. if you deduct it will add back to product table.
3. again on edit. if you typed in the wrong product code after inserting the record and you want to enter the correct product code, it update the old product's invqyt (reversing the entry). then it add/subract the qty to the new product code.
4. again on edit. if the product code is correct but you edit the qty, it will update (reversing the entry) of previous qty and add/subract the new qty you put in.

 

[MarkK]

None of this effort is required . . .

1. on new record we update the invqty with our transaction qty (plus or minus).
2. if you decide you made wrong entry and you want to delete that record, it updates the inventory quantity. reversing the process when you create the record the first time.
if you add, it will deduct. if you deduct it will add back to product table.
3. again on edit. if you typed in the wrong product code after inserting the record and you want to enter the correct product code, it update the old product's invqyt (reversing the entry). then it add/subract the qty to the new product code.
4. again on edit. if the product code is correct but you edit the qty, it will update (reversing the entry) of previous qty and add/subract the new qty you put in.

. . . if you simply calculate the balance on demand from the raw transaction data.

Choose wisely,

 

[arnelgp]

it only takes small code to do it. and besides, after using the system over years and decided to purge some transaction data, leaving on the this years transactions (backup then purge), how can you run the balance on demand?

 

[MarkK]

Why would you ever do this?

and decided to purge some transaction data

 

[arnelgp]

it is always part of business rule that you keep records for certain amount of time, say for 5 years. then after these years it will be said that it safe to delete, backup or destroy these data.
since these data are likely not to change over these periods and will stay dormant. will therefore just take space on the db. it is better to move them to another location.