my query won't select everything I need (1 Viewer)

[Wayne Jordan]

My db tracks consumable supplies for our warehouse. I have seen the light and now calculate qty on hand instead of storing that value in a table. I do the same with outstanding quantities on order. I also have a query that tells me when it is time to order. Or did. For each item I had the following fields: QOH (qty on hand), QOO (qty on order) and ROP (reorder point.) The query would select items to order where QOH + QOO < ROP. That was easy because those values came from fields in a table.
Now the values come from queries. QOH is no problem but QOO is causing problems because the query will only select items that have outstanding orders. In other words, if there is no QOO the query does not select that item. I can enter a fake record in the Order Details table to make it look like I have 1 of each item on order and that works but I know there has to be a better way. If I forget to add this fake record when we add a new sku then that item will not be included in the query.

I hope I have not made this too confusing.

Thanks in advance for any help anyone can give me.

 

[pbaldy]

Sounds like you need to edit the join between tables in your query, plus use the Nz() function to handle the Null that will come from the table with no records.

 

[Wayne Jordan]

I figured it out. Actually, I learned two things.
1. Do a more complete job of describing my calculations.
My

 

[Wayne Jordan]

Don't know what happened above.
2. My solution.
I described the overall concept but not the actual formula. It was really more like,
Sum of Received + Sum of all order details where the field "PO closed" = "no", i.e if the PO is still open count the outstanding qty, if closed don't count. Custom items from our vendors are fulfilled when +/- 10%. So a PO can be closed yet the "qty outstanding" field would still show a balance.
I have now created a query to update outstanding qty to 0 on all closed POs and removed the condition in the first query that referred to PO status. Now my query counts the qty outstanding on all POs and comes to the desired qty. So it works now. I just have to make sure that whenever an order is filled that I update oustanding qty to 0.
I apologize that my writing appears so incoherent. I guess that follows the mind of the writer.

Thanks for your help.

 

[pbaldy]

Glad you got it sorted out.