Results 1 to 9 of 9
  1. #1
    pipman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    5

    Inventory Help!!!

    Hey Everyone, I'm new to Access and this forum so please forgive me for my ignorance. I have searched this forum several times and tried some examples that are similar to my situation, but have had no luck.

    I have a small database at work for some parts that we maintain in our inventory. I have a table (StockInventory) which holds the PartNum, Noun, Location, & Qty. I also have another table where the stock is issued out (StockIssued) which has IssuedTo, PartNum, QtyIssued, & DateIssued. The third table I have is to replenish the stock (AddStock) it has ID, PartNum, QtyAdded, DateAdded. I joined StockIssued and AddStock to StockInventory via the PartNum field.

    The problem I am having is maintaining a running tally of what is currently in inventory after issuing stock and replenishing it. I have tried making a query and including all three tables. I add StockInventory.PartNum, StockInventory.Qty, StockIssued.SumOfQtyIssued, & AddStock.SumOfQtyAdded. I then made a fourth column with ActiveQty: [Qty]+[SumOfQtyAdded]-[SumOfQtyIssued]. It works, however it only works for items that have had stock issued AND replenished. If something is just issued and hasn't been replenished it won't show up. Of course if a part hasn't been issued or replenished, then the ActiveQty should be the same as the StockInventory.Qty. But nothing shows up except the items that have had both actions.

    I also tried the same thing as above without the StockIssued or AddStock tables. By doing that I had two boxes appear wanting me to give quantity's for eacy Sum field. All PartNum in the inventory showed up by doing this, but I got those annoying boxes/prompts.

    Can anyone help me with this one to see what I'm doing wrong. I'm ready to pull my hair out over this. The boss wants it ready to start on Tuesday when we start the new year's inventory.

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    The problem you are experiencing is that when there is no value for Access to calculate (the record is Null), it doesn't know what to do, so it does nothing. What you need to add to your calculations is a message to Access to tell it what to do when it experiences this situation. A function called NZ does just that. Here is a link on how to employ the NZ function.

    http://www.techonthenet.com/access/f...dvanced/nz.php

    Good Luck and Happy New Year.

  3. #3
    pipman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    5
    alan,

    I had seen something about the Nz function. However when I tried to insert it in my query as Nz(Qty,0) I got an error message. Where exactly would I put the Nz function in the Query where the calculations are at? If so which column? Would I put it before the calculation for the sum of the column, if so what would be the syntax for that? Maybe I just didn't type it in right.

    Thanks for the help!!

  4. #4
    pipman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    5
    I tried to put Nz(Qty,0) in the criteria for the fields and I just get an error message. I've also tried to place it in the Field before the DSum, but that didn't work either, or maybe I just don't have the syntax correct. Can anyone help with this please!!

    Thanks in advance for any help!!!!!

  5. #5
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    Try this:

    ActiveQty: NZ([Qty],0)+NZ([SumOfQtyAdded],0)-NZ([SumOfQtyIssued],0)


    You want to put it where you are doing your calculation.

  6. #6
    pipman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    5
    Thanks alan,

    I tried that, but it still didn't work. Could it be because there is only a handfull of parts in that have been issued and/or replenished?

    I even tried to put all the part numbers in the issued and add tables with a 0 for the Qty. Now I am getting the pop up windows again asking me to input the numbers before it opens up the query.

    I uploaded part of what I'm doing so ya'll can take a look at it.

    Thanks!!

  7. #7
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    See attached. I changed your query to make your joins from Inner to Outer (Left). Next I put the calculation in a new query that was dependent on the first query. Just cleaner IMO and it lets you see what is happening.

    Hope this is what you were looking for.

    Alan

  8. #8
    pipman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    5
    Thanks alan, you're a life saver!! That worked great!! I don't really understand why dividing it up between two queries instead of the one makes a difference.

  9. #9
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    I know that it can be done with one query but I cannot remember if you have to do a subquery or if you need to put something special in the where clause to make it work because of the Group By issue. Because I can't remember, I just make it two queries and I know that it will work and will not take any longer to process. If someone with more expertise looks at this thread, they perhaps can give both of us an educated answer.
    Alan

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

Similar Threads

  1. Inventory
    By Nemacol in forum Access
    Replies: 1
    Last Post: 09-13-2011, 08:24 PM
  2. Inventory
    By Nemacol in forum Database Design
    Replies: 2
    Last Post: 09-13-2011, 05:23 PM
  3. help inventory db
    By mesersmith in forum Database Design
    Replies: 3
    Last Post: 03-10-2011, 11:48 AM
  4. Inventory
    By thisandthat in forum Access
    Replies: 3
    Last Post: 03-01-2011, 08:09 PM
  5. Subtract from inventory
    By NISMOJim in forum Access
    Replies: 5
    Last Post: 01-30-2011, 01:09 PM

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