4
I'm having trouble getting this query worked out. In my table, the relevant fields (with descriptions) are:
SKU(the Item Number), StorageType (the type of rack the item is stored in, IE: pallet, half-pallet), Location (obvious), QuantityPicked (obvious), Area ('Front' or 'Back' of the warehouse)
What I need to do is find instances where the QuantityPicked is 0 and the Location is Front (meaning the item is sitting near the front of the warehouse and not being sold) and swap it with items where the QuantityPicked is >0 and the Location is Back (meaning the item is being sold actively and it is stored in the back of the warehouse). Additionally, the StorageType must match (ie: I don't want to try to fit a full pallet into a half-pallet slot, or vice-versa).
Every permuatation I come up with ends up with items being duplicated as it tries to synch up the StorageType. I'll get a copy of every item that has a quantity for every location where the StorageType matches up with an item location that doesn't have a quantity.
If I have 3 pallet items with a quantity and 5 pallet items without a quantity, I'll get 15 records returned. What I would like to see is 3 records returned, matching those with a quantity to a location without one (and then either ignoring the remaining two empty slots or just pairing them with a Null.
At this point I'm thinking of weird solutions like creating multtiple recordsets and doing some sort of weird filtering when generating the report, but I'm not sure if that's just the exhaustion speaking or if there really isn't a better way.
Any advice or pointers would be greatly appreciated!
