Stock update in access 2010

[s60293jp]

Hi,


I have prepared two access database named "Stock" (field - "S_ID", "Item_Name", "StockQty"), "Received" (field - "R_ID", "Item_Name", "ReceivedQty").
Now my question is how can I update the Stockqty with the help of ReceivedQty? Is there any VBA code which will help me to solve the problem.

Thanks

Regards
s60293jp

[orange]

Normally we refer to a Database that contains Tables. In you case, it appears yo have table Stock and table Received.
If you want to UPDATE a field(s) in one table based on values in a related table, you would use an UPDATE query.

What exactly is the field that relates these tables?

Have you considered those Transactions where items are removed from Stock and sold to Customer....?

research/google "SQL Update query 2 tables example"

[s60293jp]

Thank for your reply
1) The tables are relationed on "S_ID" from Table Stock and "R_ID" from Table Received as one to many.
2) Transactions - items are removed from Stock and sold to Customer would be done through another table named "Table Issue" where the relation with the "Table Stock" is on "S_ID" and "I_ID" as one to many.
3) I have gone through "SQL Update query 2 tables example", but here I need only VBA Code. Is there any link to wite the SQL statement here? I have no SQL server.

Thanks
Regards
s60293jp

[orange]

I'm not following your comments.
You can have a TblInventory ans a TblTransaction.

POsitive transaction will increase Inventory, and negative transactions reduce inventory.

[s60293jp]

Hi everybody,

Yes, your are right.

thank you

regards

s60293jp