Hi everyone, so I've been trying to do something for a while and I haven't quite been able to figure it out. I work off a database that uses tables, and I use MS query to design queries that track our inventory levels. Here is what I've been able to accomplish successfully so far:
IIF((IC_ITWH.QTY_OHND+IC_ITWH.QTY_OORD-IC_ITWH.QTY_CMTD)>IC_ITWH.EOQ_QTY,Null,'Reorder')
So in this formula, If the quantity we have on hand plus the quantity we have on order minus the quantity we have committed to orders is greater than the minimum level that I set for each part, then nothing is returned. If it is less than the minimum, then "Reorder" is displayed. The problem is, we have multiple warehouses. Unless a part is ONLY in one warehouse, then I get duplicate entries on the same part number, (one with "Reorder" and the others with "Blank". I have tried to figure out a way to combine the totals of the all of the warehouses into one entry but it doesn't work. If I leave out the above formula, the query works fine, and combines the totals from all warehouses. The problem comes when I try and add this formula. Instead of totaling up the totals from all the warehouses before running the IIF function, it does each warehouse individually, returning multiple entries
Does anyone have any ideas on how to fix this issue? I feel like I am close but I just can't quite get it.
Thank you,
IIF((IC_ITWH.QTY_OHND+IC_ITWH.QTY_OORD-IC_ITWH.QTY_CMTD)>IC_ITWH.EOQ_QTY,Null,'Reorder')
So in this formula, If the quantity we have on hand plus the quantity we have on order minus the quantity we have committed to orders is greater than the minimum level that I set for each part, then nothing is returned. If it is less than the minimum, then "Reorder" is displayed. The problem is, we have multiple warehouses. Unless a part is ONLY in one warehouse, then I get duplicate entries on the same part number, (one with "Reorder" and the others with "Blank". I have tried to figure out a way to combine the totals of the all of the warehouses into one entry but it doesn't work. If I leave out the above formula, the query works fine, and combines the totals from all warehouses. The problem comes when I try and add this formula. Instead of totaling up the totals from all the warehouses before running the IIF function, it does each warehouse individually, returning multiple entries
Does anyone have any ideas on how to fix this issue? I feel like I am close but I just can't quite get it.
Thank you,