Hello! Please help me with MSQuery Formula (I'M SO CLOSE!"

Aero13

New Member
Joined
Mar 16, 2017
Messages
4
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,
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top