Inventory Help!!!

[pipman]

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.

[alansidman]

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.

[pipman]

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!!

[pipman]

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!!!!!

[alansidman]

Try this:

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


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

[pipman]

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!!

[alansidman]

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

[pipman]

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.

[alansidman]

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