Results 1 to 6 of 6
  1. #1
    BEubanks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    5

    How to make inventory transaction table

    I am very much a newbie at Access--I've watched about 7 hours of Lynda.com videos, but that's as much "formal" training as I have had. My boss has asked me to make an inventory database for our tools and supplies, and from what I've gathered elsewhere, best practice would be to have an inventory transactions table with positive and negative transactions. I've been trying to reverse engineer the "Inventory" template in Access, and I keep running face first into a wall. It has a table that specifies that Addition = Addition, Removal = Removal, and Shrinkage = Removal, and those 3 types of transactions are allowed in the transactions table. When you select either of the removal types, it automatically makes the number you input negative. But I cannot for the life of me figure out how it is set up and where it specifies to make that a negative number!



    Can anyone break down for me how this function works in the template and how to recreate it in my own database?

    TLDR: How do I create an inventory transactions table that adds and subtracts items in an inventory so I can calculate a running inventory level?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Which template exactly?

    A totals query will combine all transactions by item and sum up the amounts:

    https://support.office.com/en-gb/art...4-8c8dbbe41c8a
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by BEubanks View Post
    ......... But I cannot for the life of me figure out how it is set up and where it specifies to make that a negative number! .............
    Take a look at the query used as the forms Record Source. You will see that Actual Quantity is a calculated field which uses the IIF() function to input the minus sign
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I note that you are an admitted "newbie" to Access. Inventory is not a trivial subject as has been reported many times in the various forums. Here is a link to a 26 minute free youtube video that deals with Stock Management.
    This doesn't resolve your specific problem, but it does go through the process of design and development of an inventory system. It will show you the things involved generally -- you should get a better understanding of what is involved.

    Here is a link to Allen Browne's article on Inventory which is an often referenced link, especially as it deals with "Quantity on Hand".

    Good luck with your project.

  5. #5
    BEubanks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    5
    "Take a look at the query used as the forms Record Source. You will see that Actual Quantity is a calculated field which uses the IIF() function to input the minus sign"

    There it is! The missing piece! I may not understand it yet, but that is exactly what I was looking for. Thank you so much!

  6. #6
    BEubanks is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    5
    Quote Originally Posted by orange View Post
    I note that you are an admitted "newbie" to Access. Inventory is not a trivial subject as has been reported many times in the various forums. Here is a link to a 26 minute free youtube video that deals with Stock Management.
    This doesn't resolve your specific problem, but it does go through the process of design and development of an inventory system. It will show you the things involved generally -- you should get a better understanding of what is involved.

    Here is a link to Allen Browne's article on Inventory which is an often referenced link, especially as it deals with "Quantity on Hand".

    Good luck with your project.
    I know it's not a trivial matter (though my boss doesn't seem to realize it...) but I so appreciate the resources so I can look at it a little deeper and at least say I've given it my best shot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-30-2018, 11:27 PM
  2. Replies: 2
    Last Post: 02-06-2018, 07:07 AM
  3. Replies: 4
    Last Post: 10-27-2014, 12:17 PM
  4. inventory system transaction form fields
    By xtrareal22 in forum Forms
    Replies: 2
    Last Post: 11-26-2013, 01:06 PM
  5. Replies: 1
    Last Post: 02-21-2013, 12:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums