How to add columns from one table to another based on the Property Control value

[Peter Fleege]

I have two tables - one main table with sales data on District level, and another table with geography details (Sales Area, District, Sales Team).

Each Sales Area contain certain Districts, but these Sales Areas differ in structure based on Sales Team name. E.g.:

 

Sales Team "Alpha" is responsible for 3 areas:

 

Area A, which containdistricts 1, 2 and 3

Area B,which containdistricts 4, 5and 6

Area C, which contain districts 7, 8 and 9

 

 

Sales Team "Beta" on the other hand is responsible for:

 

AreaD, which containdistricts 1, 4and 6

Area E,which containdistricts 2, 3 and 9

AreaF,which containdistricts 5, 7and 8

 

 

 

What I need to do is to add a column (and replace the column content of the same column when the value in the Property Control changes)to the main table with this Sales Area information (matching District columns in both tables). The Sales Area information should be picked from the second table based on the value in the Drop-down Property Control list.

Any thoughts on how to make this work