More Using MS Access Answers
Question Library
Ask a question about Using MS Access
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Sayedaziz
Expertise
database designing,event driven programming,capturing/updating data programmitically using ms access 2000 and ms access 2002
Experience
12 yrs. as a vb programmer
Organizations
Print Media
Publications
Math Skill Test VB6 Game Source Code published at Planet-Source
---------------------------------------------------------------
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=72322&lngWId=1
Holy Quran Source Code published at Planet-Source
-------------------------------------------------
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=1&txtCodeId=72343&txtForceRefresh=82020091635140766
Right to Left Treeview & Listview Source Code published at Planet-Source
------------------------------------------------------------------------
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=72376&lngWId=1
Education/Credentials
B.Com, AICWA (Associate member of Cost & Works Accountants Of India)
| | |
| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Inventory tracking database
Expert: Sayedaziz - 11/3/2009
Question
Hi Sayedaziz.
I am very curious to make an inventory tracking database.I have created tables such as Product details which contains(ProductID,ProductName,Price),Purchase Table(PurID,ProductID as forign key,Quantity Purchased),Sales Tabel(SalesID,ProductID as forign Key,Quantity Sold) & Stock Table(ProductID as forign key,ProductName,Quantity in Hand).My requirement is that whenever i enter quantity purchased in Purchase table it should get reflected in stock table and same thing should happen when i enter quantity in sales table.I hope the database structure is correct.
waiting 4 ur reply.
Answer
In my opinion Stock Table should be removed and add 2 fields in Product table like OpeningQty (every establishment has opening qty if it is a new one it will have opening qty next year) and AvailableQty. The Data entry in Product form will update the AvailableQty field with OpeningQty since at begining of the year, AvailableQty would be the same to the OpeningQty.
When user update Purchase form in Form After update event use the following Code:
Docmd.RunSQL "UPDATE tblProduct SET tblProduct.[AvailableQty] =" & Me.purchaseQtytxtboxname &
" WHERE tblProduct.ProductId = " & Me.ProductIdTextboxName
Add to this Answer Ask a Question
|
|
