Using MS Access/adding stock

Advertisement


Question
Hello,
I am a very beginner with Access. I am trying to design a database to control stock.
I have a simple problem, but I can't find the answer when I google it.
I have a table with stock on hand. Each product has a unique batch number. I want to create a form in wich I can find the product by batch number with an empty field for quantity. In that field I want to be able to add a number. That number then should then be added to the quantity in my stock table. The next time I use this form, the field for quantity should be empty again for adding a new number etc.
I hope I explain it clearly.
Is there an easy solution for this?
Many thanks,
Hilde

Answer
That's not the best way to handle stock control. What you need is a transactions table like:

tblTransaction
TransactionID (Pk Autonumber)
TransDate
ProductID (FK)
TranstypeID (FK)
Quantity

You also need a table For Transaction Types

tluTransType
TransTypeID (PK Long Integer)
TransType
Direction (either in, out or neutral)

Now you create a form bound to tblTransaction. Every time you move stock you enter a transaction using that form.

From there you CALCULATE stock on hand, using  a query that adds the incoming transactions and subtracts the outgoing transactions.

See http://allenbrowne.com/AppInventory.html

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.