I need to assign the correct Manager to a table of sales data in MS-Access. The assignment is based on multiple criteria and I would appreciate your help with the correct SQL syntax.
The first criteria is matching the record based on the STATE where the transactions was sold.
The second criteria will override the first and is based on an Overlay rep being the selling rep - they can sell in any State.
The third criteria will override the first and the second where the selling rep is the manager themselves.
If no match can be found from any lookup then the Manager is blank or "None" is acceptable.
The attached Access file has sample data that can be used and it contains:
Tbl_Data - the table of sales data
Tbl_ST - the list of States and the Manager responsible
Tbl_Overlay - the list of Overlay reps and their Manager
Tbl_Mgr - the list of Managers
RESULTS - This table contains the results I am trying to get to via the SQL Query or Queries
My SQL knowledge is mostly drag and drop in Access but I can usually read and understand the SQL once it written. The results can be a single SQL query or multiple queries to get to the end result. Either is fine.