I'm currently stumped by what, I think, is a very easy problem. Ultimately, this is a simple VLOOKUP:
Table 1: Orders
Field: City (non-unique tex, repeatedt: Atlanta, Baltimore, Chicago )
Order Number (unique): 123, 234, 345
Table 2: Collection Agents
City (unique text)
Agent (non-unique user, each agent may have multiple cities)
It seems I'd need a table to table relationship of one collection agent to many orders. City in Table 2 being a key field.
Now, I want to pull in the Agent from Table 2 into Table 1 automatically.
But, I'm blanking on how to make this happen using city as the linkage.
Your help is much appreciated.