Hi everyone, having a problem with a query I'm designing.
I am working off a inventory database that our system software contains. Here are the columns:
Item number
Description
Current Stock
Quantity on Order
Order Point Quantity
Everything is working well with these. The item numbers come up, as well as their description, current stock level, the quantity we have on order of each part, and the order point quantity, which I can set to whatever value I want.
Here is where the problem comes in. I have written the following code into another field:
IIF((IC_ITWH.EOQ_QTY-(IIF(IC_ITWH.QTY_OHND Is Null,+0,IC_ITWH.QTY_OHND)+IC_ITWH.QTY_OORD))>0,'REORDER',Null)
It does work. Basically if the end user quantity that I set, minus the sum of amount on hand plus the amount on order, is greater than zero, "Reorder" is displayed. If not, then no entry is made.
The problem I'm having is that the ones that show "Reorder" duplicate the entry on the query, and I know why it is happening. We have 2 warehouses in house, "new" and "old". There's another field called "warehouse number" that I left out of the query so that the parts will hopefully sum together without separating out between the new and old. For some reason, if an item number contains a record of both the "new" and "old" warehouse, a duplicate entry is listed in the query results. How do I combine the two warehouse totals so that the query calculates based off the total sum, without creating a separate entry for both warehouse? It's weird that this only happens on a calculated field. Just listing the part numbers by themselves does not generate this issue.
Really hoping someone can help. Thank you very much,
I am working off a inventory database that our system software contains. Here are the columns:
Item number
Description
Current Stock
Quantity on Order
Order Point Quantity
Everything is working well with these. The item numbers come up, as well as their description, current stock level, the quantity we have on order of each part, and the order point quantity, which I can set to whatever value I want.
Here is where the problem comes in. I have written the following code into another field:
IIF((IC_ITWH.EOQ_QTY-(IIF(IC_ITWH.QTY_OHND Is Null,+0,IC_ITWH.QTY_OHND)+IC_ITWH.QTY_OORD))>0,'REORDER',Null)
It does work. Basically if the end user quantity that I set, minus the sum of amount on hand plus the amount on order, is greater than zero, "Reorder" is displayed. If not, then no entry is made.
The problem I'm having is that the ones that show "Reorder" duplicate the entry on the query, and I know why it is happening. We have 2 warehouses in house, "new" and "old". There's another field called "warehouse number" that I left out of the query so that the parts will hopefully sum together without separating out between the new and old. For some reason, if an item number contains a record of both the "new" and "old" warehouse, a duplicate entry is listed in the query results. How do I combine the two warehouse totals so that the query calculates based off the total sum, without creating a separate entry for both warehouse? It's weird that this only happens on a calculated field. Just listing the part numbers by themselves does not generate this issue.
Really hoping someone can help. Thank you very much,
