inventory table with a running total

I have 2 tables.  One table is an inventory table and has two fields
"tblItem name" and "tblItem quantity".  The other table which is called "tblInventory Out" is a record of all inventory items that are given out to users and has 7 fields which are "Description", "Quantity", "Date Out", "Date In", "Dept", "Signed Out By", "Signed In By" you probably don't need all these fields but they are  there if you do.  

What I need to do is when an entry is made in the Inventory Out form in the  field name description  say "Printer" and in the quantity field  "4" I want to be able to automatically deduct 4 from the quantity field in the Inventory table and keep a running total of that item in the Inventory Out form. So when a user makes an entry, the information is read back to them immediately. Such as if there were 20 printers now there would be 16 listed.  As an added attraction it would be nice if the user was notified if there were not enough of an item or the item requested is gettin low in stock.

Thanks  

Charlie
charliebccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

charliebccAuthor Commented:
Adjusted points to 100
0
JimMorganCommented:
Hi Charlie:

This looks fairly simple to do.  For your inventory out form, make the records behind the form a query which joins the inventory table and the inventory out table by common field.

At the top of the form put the total quantity available, which is the quantity field from the inventory table.

(I would also redesign the structure of these tables but that is not covered by the question.)

If you had another field in inventory table which indicated the low stock level, then when the item is selected, you can test against the low stock field and turn on a hidden label which says "Low Stock".

In the After Update of the quantity to take out, calculate the new quantity in the inventory quantity based on the out value.  

  With [TblItem Quantity]
    .Text = [TblItem Quantity] - InvOutQty.
    If .Text < 0 Then
       lblNotEnoughStock.Visible = True
       .Value = .OldValue
       InvOutQty = 0
    End if
    If .Text < QtyLow Then
       lblLowStock.Visible = True
    End if
  End With

This is just a rough cut and will need some tweaking but since I don't know your DB, this should get you started.

Jim
0
charliebccAuthor Commented:
Hi Jim:
Sorry for the delayed reply but I was on vacation and could not get to my email.  Could you be more specific about changing the structure of the tables.  Nothing is wriiten in stone and I can change them if it would help.
I am also a little foggy on what kind of common field to use for the query.

Any suggestions would be appreciated.

Charlie
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JimMorganCommented:
Was it a nice vacation?

Essentially a PartID is an example of a common field to pull all of this together.  Dealing with parts and inventories, to flesh out the basics that you need from a structure standpoint is something like this:  (only a suggestion)

    Table        Key Field     Comments

    Parts        PartID        Static information describing a part.

    Transaction  TransID       Also has a PartID to link back to the parts table.
                               Dynamic information regarding parts removed from inventory.

    Inventory    PartID        Dynamic quantities on hand (and on order, etc.)

Parts: the PartID is the key to link this information to other tables.  PartID is not important to the user.  Strictly an internal identifier.  Set it up as an AutoNumber field and let Access assign the numbers.

Transactions: The TransID makes each transaction unique.  Helps in indexing.  Should be an autonumber field.  The PartID field, Long number, links to which part and inventory record the transaction belongs.

Inventory:  The PartID is a unique key field.  It is just a Long number.  This could be put into the Parts table but it probably is better to keep it separate.

From a relational viewpoint, a part can have multiple transactions but only one inventory record.

Inventory Table                        Parts Table                     Transaction Table
   PartID <1-------------------------1>  PartID  <1------------------Many>   PartID
          Enforce Referential Integrity         Enforce Referential Integrity
          Cascade Update                        Cascade Update
          Cascade Delete                        Cascade Delete

In the underlying query for the form where you add tranactions, you would use all three tables in order to link the transaction to the inventory quantity.  This will allow you to see the PartName, PartNo, Description, etc.  Make the entry and then update the inventory quantity.

When you enter parts in the Parts Table, Access will automatically create an entry in the inventory table.  I'd do this with a form and query as well so that you could enter the initial inventory levels.

Thats quite a bit to digest tonight.  Look at it, think about it, and come back with more questions, if you have any.

Jim

0
charliebccAuthor Commented:
Dear Jim:

Just read your message and you are right.  I will get back to you after I digest these well written instructions.

Thanks

Charlie


0
JimMorganCommented:
Charlie:

Looking forward to it.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
charliebccAuthor Commented:
mission accomplished.

ready for the next part.

thanks

charlie
0
JimMorganCommented:
I assumed that 'mission accomplished' meant that this question was answered.  However it was unclear with the next remark 'ready for the next part'.

So rather than answer I just gave this comment.  Any time that you feel that I have given you enought to solve your question, you can always make any of my comments as an answer.  There is a click point on the right top of any 'Comment' title bar.

Jim
0
charliebccAuthor Commented:
I got it.  I wasn't sure if there was a next part or not.  I think I have enough to do what I need.  If not I'll be back.  Thanks very much for your help.

Charlie

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.