The second table, call it a substitution table, is as follows:
The substitution table has a dual role:
1. If ItemOld is found in the base table, while its corresponding ItemNew is not, then simply replace the Item number in the base table with ItemNew.
2. If ItemOld is found in the base table, and its corresponding ItemNew is also in the base table, then;
a) replace the Item number with ItemNew
b) exclude records where the week of Item is not matched by the same week for another record with the same ItemNew
So the final result would look like this:
Item Week Qty
222 2 20
222 2 30
222 3 40
444 3 50
Only 1 record was deleted, the first record because Item 111 with week 1 did not have a corresponding record of 222 with the same week number.
My apologies for such a lengthy question. I tried to do this completely with queries, as that is necessary in my case. In the end I used a total of 6 select queries, however, it seems like that might be inefficient and too much.
there's a simple solution for the replacement; by joining the tables in a query and using a new calculated value/column with some iif logic. One can display the replacement value rather than the original value without a problem.
I don't think there is a simple solution for the second part where you want to exclude a row. It would be simple if a human was involved. But it is going to be a complicated sql statement to dynamically compare value sets across a dynamic group. Off the cuff I see you want to exclude the minimum but crafting that sql isn't something at my fingertips.