How to manage tables in a query?

I have this table that assigns loans to collectors by officer number… works well. An issue I have now is that some accounts should not be worked by collections.

I have officer 99999 assigned to John. The officer prefers we exclude a few accounts from being collected. So I thought I could add a column to my table to include account numbers & have those account numbers assigned to “OFF” instead of “John”. It doesn’t work 100%, if you look at query 1 it only assigns loans to “OFF” & doesn’t assign the other loans.

Oh & excluding the loan is not an option.

Couldn't think of a proper title...

You only have two accounts in OFF *** that have account numbers. The other two records have no account number and therefore cannot be assigned.

I thought I could then update by officer number after it found those two account numbers. I've written IIf/Replace funtions & tried an update query but that didn't work. Not sure if this is even possible... I just want the assignments to be automatic.

Help me to understand what you want to do. Please explain simply what you are trying to accomplish and perhaps we can give you a viable solution. Play out the scenarios and if possible show what the end results should look like (you could use an excel spreadsheet to show the expected result).

Because you have no account numbers in the OFF file except for the two and you have joined the account numbers in both tables, nothing will appear because there is no relationship established. You will need to generate an expression that states something like this.

Collector: IIF([Coll]="OFF","OFF","John")

If you are looking to to update a tables then you will need to create an update query. If you are just looking for a report/query, then the expression should be sufficient