Help with Access SQL - Assign Manager to Records Based on Multiple Criteria
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
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
ASKER
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
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
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?
-
ASKER
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
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;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
I love Switch, and find it much, much easier to understand than embedded IIf expressions.
I wish Excel had something like it :)
Patrick
ASKER
Yes. I can think of several IIF statements I am going to try to replace with Switch.
Thanks
Jerry
Thanks
Jerry
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