You are here:

Using MS Access/MS-Access 2007 inventory management


Hi Manish,

How are you ?.

i am trying to develop inventory management software for my personal purpose in MS-Access 2007.

my problem is as below.

have one table item_inward with below fields.. ID,date,item_name,purchase_rate,inward_quantity,outward quantity

1.) There is possibility of quantity remains in line_id 1 and other line 2 is added for same item (i.e ID=1 , date=01/01/2016 , item_name="Shoes",purchase_rate=1000.00,inward_quantity=500,outward_quatity=400------ID=1 , date=01/01/2016 , item_name="Shoes",purchase_rate=1000.00,inward_quantity=300,outward_quatity=0)
2.) when any item is outward and quantity = 1. i have set dlookup formula which will get smallest id and outward_quantity is less than inward_quantity. and 1 quantity will be added in found id. same is a normal scenario.
3.) now if quantity is more than 1 (i.e say 50) and remaining quantity against inward_quantity is less say (inward_quantity = 500,outward_quantity=470 so balance quantity for this line is only 30). my code dosent update anything.

4.) i need solution (loop) where system will update value in lines by first in first out basis which is mentioned in above points.(i.e if total outward is = 50 nos.  balance quantity in line 1 is 30 so line 1 quantity + 30 in line 1 to be updated and remaining quantity to be updated against line 2 and so on to other lines till outward quantity)

I wish you will understand my problem from examples given above.

Vimal Thakar

Hi Vimal,

In fact in a normal inventory system we have 2 forms, Purchases(Goods Inward) and Sales (Goods Outwards). But in your case I'm not seeing a Sales Form to record Goods going outward.You are trying to calculate the balance on hand in the item_inward table itself which is not a good practise.

Your solution lies in creating a Sales form, where you record all the products that are being sold. When each product is sold you need to fire an update query to update the current stock of that product being sold on saving the transaction from that form. This will help you get a ready stock position count whenever required by yourself or anyone on the network.

Your current practise is more like a spreadsheet and you will not be able to perform the activity your looking for by using a spreadsheet style.

Do let me know.

Best regards

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Manish Batola


ALL MICROSOFT ACCESS DESIGN AND Development QUESTIONS which occur in a Access Projects Lifecycle. Can offer constructive help on Designing Advanced MS-ACCESS Applications such CRM/FRM(Follow-up),HSE-Health,Safety & Environmental Applications,School Management Systems,Hotel Software, Hospital Software,Chemicals Indenting,Import/Export,MRP - Manufacturing Resource Planning, ERP - Enterprise Resource Planning, Automotive Management Systems, Business Intelligence for Oil & Gas Drilling Operations, CRM,KPI- Key Performance Indicators,HealthCare, Hospitality, Constructions, Loyalty Clubs, Call Centers, Travel & Tourism, Educational Institutions, Industrial Strength Invoicing and many more being added every year.


29+ yrs in the Software industry with 23yrs completely dedicated on more than 350 Various Microsoft Access Projects from different Industry Sector[s]. I'm using Access ever since it was born.

Worked in various multinationals catering from Aerospace, Oil & Gas etc.

-BSc - Computer Science -MSAP (Master in Software & Applications Programming) -BPRE (Business Process Re-engineering Engineer) -SQAE (Software Quality Assurance Engineer) -STIE (Software Testing & Inspection Engineer) -Project Engineer (Software Development)

Past/Present Clients
Catered to various clients in the following industries • Oil & Natural Gas (BPM/CRM/Access Development) • Sales / Marketing (CRM) • Automotive (Automobile Dealers) • Logistics & Indenters (Import/Export) • HealthCare / Hospitals • Hotels 3* & 5* • Restaurants • Construction Co’s (Project Billing) • Clubs (Concept Selling) • Shoe-Chains • Solicitors/Lawyers • Call Centers • Aerospace (Preventive Maintenance) • Travel & Tourism • Computer Vendors • Manufacturing • Music Shops • Education • Data Warehousing • Health,Safety & Environmental Applications • Mud Engineering • School Management Software

©2016 All rights reserved.