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.