How can I use relationships for this simple VLOOKUP?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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.

Photo of Larbi


  • 20 Points

Posted 3 years ago

  • 0
  • 1
You will need to make a table of unique cities.  

Set the Key field to be the city name

Then a relationship where 1 agent has many cities based on the reference feld on the right being the city name.  Lookup the agent down to the city.

Then a relationship where 1 city has many orders based on the refence field on the right being the city name.

Lookup the agent down to the order

.. And y'ur done
Photo of Larbi


  • 20 Points
So, let me get this straight:

Table 1: Orders
   Field 1: Order Number (unique, key field)
   Field 2: Order Number City (non-unique)

Table 2: Agents
   Field 3: Agent Name (unique, key field)

Table 3: Cities
   Field 4: City Name (unique, key field)


Orders < Agents (reference: related order, lookup Field 1)
Agents < Cities (reference: related agent, lookup Field 3)
Cities < Orders (reference: related city, lookup Field 4)

But I'm struggling to see how we use the non-unique city in table 1 (orders) to pull in the agent name.
You will not have that first relationsip.  The Agent information, say Agent name,  lives in the Agent Table and will be a lookup field down to City.  Then 1 City has Many Orders and then lookup the lookup of the Agent name down from City down to the Orders.