Help with Access SQL - Assign Manager to Records Based on Multiple Criteria

Jerry Paladino
Jerry Paladino used Ask the Experts™
on
Hello Experts,

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.

Thank You,
Jerry

Sample.mdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try

SELECT Tbl_Data.Rep, Tbl_Data.Cust, Tbl_Data.ST, Tbl_Data.Amt, Tbl_ST.Mgr
FROM Tbl_Data INNER JOIN Tbl_ST ON Tbl_Data.ST = Tbl_ST.State;


see query1 from the sample
Sample.mdb
capricorn1,
Thank you.  yes the new query takes care of the State lookup but does not take into concideration the second and third criteria.  
The second is where an OverLay rep can cell into any state.  The Nancy and Debbie records should be assigned to Judy based on the Tbl_Overlay matching instead of Frank in your query.  See the RESULTS table for what the final results should be.
The third criteria is where the manager is the selling rep and they should be assigned as the manager on those records.  In my example tables they happen to match the State lookup values but could override the State assignment.
Jerry

Commented:
Read Carefully, your answer is in your question;
  • If the current value of manager other than None and should be overridden
    or in other words. are we re-assigning manager per these criteria?
  • Reverse the order of your questions.
    • The third criteria will override the first and the second where the selling rep is the manager themselves.
    • 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 first criteria is matching the record based on the STATE where the transactions was sold.
      But what about the state defines the manager, Most sales?
  • What is the Client being used to communicate to the accessdb. Do we have a Form that might have a button?
  • Is there going to be a manager column in tbl_data, or is this an extract?
  • Do we want a roll-up of results, like a report?

Open in new window

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Banthor,
The data does not contain the Manager name and I am trying to assign it based on the criteria.
I am currently solving this in Excel with lookup tables and compound IF statements and yes, you are correct in reversing the order which is how I get the results in Excel.  Check manager first, then check the Overlay reps, then check the State table.  If all tests fail the manager is left blank.
It is an Excel application that uses ACCESS as a query engine to manipulate data and then Excel retrieves those result sets via OLEDB or ADO.  I only need the original data with the new column "MGR" added to in a result set that I can retrieve with Excel.
Please don't laugh at my elementary attempt in the code box below but I can approximate what I want by doing a State lookup then taking that result set and doing an Overlay Rep lookup, and then taking that result set and doing the manager lookup.  I get the results but in three separate manager columns which obviously is not a workable solution.   I need all three tests run and end up with a single MGR column added to the data.
Jerry
 

SELECT Tbl_Data.*, Tbl_ST.Mgr
FROM Tbl_Data LEFT JOIN Tbl_ST ON Tbl_Data.ST = Tbl_ST.State;

SELECT Query1.*, Tbl_Overlay.Mgr
FROM Tbl_Overlay RIGHT JOIN Query1 ON Tbl_Overlay.Rep = Query1.Rep;

SELECT Query2.*, Tbl_Mgr.Mgr
FROM Tbl_Mgr RIGHT JOIN Query2 ON Tbl_Mgr.Mgr = Query2.Rep;

Open in new window

Top Expert 2010
Commented:
Jerry,

This seems to be matching up with your results.

Patrick


SELECT d.ID, d.Rep, d.Cust, d.ST, d.Amt, 
    Switch([m].[Mgr] Is Not Null,[m].[Mgr],[o].[Mgr] Is Not Null,[o].[Mgr],
    [s].[Mgr] Is Not Null,[s].[Mgr],True,"<No match!>") AS Manager, 
    Switch([m].[Mgr] Is Not Null,"Manager override",[o].[Mgr] Is Not Null,"Overlay",
    [s].[Mgr] Is Not Null,"State",True,"<No match!>") AS Reason
FROM ((Tbl_Data AS d LEFT JOIN 
    Tbl_ST AS s ON d.ST = s.State) LEFT JOIN 
    Tbl_Mgr AS m ON d.Rep = m.Mgr) LEFT JOIN 
    Tbl_Overlay AS o ON d.Rep = o.Rep;

Open in new window

Patrick,

You are amazing!   Thank you very much!   I have never seen the Switch Function and had to read the help screen to see what it was doing.   Something I will be able to use again.  

Hope you are doing well.

Thanks,
Jerry
Top Expert 2010

Commented:
Glad to help, Jerry!

I love Switch, and find it much, much easier to understand than embedded IIf expressions.

I wish Excel had something like it :)

Patrick
Yes. I can think of several IIF statements I am going to try to replace with Switch.
Thanks
Jerry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial