Help with a conditional DLookup expression

[jpjordan357]

Good morning, everyone. Here's what I have going on.
I'm building a database that, among other things, contains an employee database. Aside from the usual first name, last name, username, etc, we have employee roles, which is one of my records that needs to be searched, as well as location. I am trying to populate a 'Manager' field based on the 'Managing Role' field. I have a query that weeds out managers to use as a source lookup. The reason i do this, is that the Manager changes, but the Managing Role doesn't, therefore when a manager changes (say, an IT manager), the query will always display who holds that position. For the most part, this works fine; because all but 9 of the 'ManagingRoles' are unique (Corporate Level). The problem i'm running into is the other 9. they all hold the same title but for different locations. So i'm trying to make two things:
1) A lookup field that will look at the location i've set for the employee, the managing role, and then select the appropriate name from a query that has the list of location managers.
2) An IIF statement for the query to pick the correct 'calculation', as it were. I believe I have this part already figured out, but since i can't figure out #1, this one doesn't work.

Here's what I have so far. My dlookup expression for returning a Manager field:
DLookUp("[qryManagement]![ProperName]","qryManagement"," [qryManagement]![RoleName] ='" & [ManagingRole] & "'"))

qryManagement is my query that filters out managers, ProperName is just the expression field within that combines FirstName and LastName into Last, First.
RoleName is the check value in the management query, and then ManagingRole is what checks against RoleName. This works every time:

This works great, except for, as I mentioned, the branch manager role. as you can more or less see above, record 10 and 14 hold have the same managing roles, but they are at different locations, so record 10's manager should show as record 9's name (as it does), and record 15's manager should show as record 13's name, because they are at the same location. I tried to accomplish this with the following expression, but no matter what I do, record 9's name always comes back as 'Manager' for the 'Branch Manager' managing role:
DLookUp("[qryBranchManagers]![ProperName]","qryBranchManagers"," [qryBranchManagers]![Role]='" & [ManagingRole] & "'" And "[qryBranchManagers]![Location] ='" & [Location] & "'")

The above references qryBranchManagers which filters out only those active employees with the branch manager roles and their locations. However, whenever I do this, anyone with the 'Branch Manager' set as their 'Managing Role', Record 9's name populates as the manager, probably because it shows up first in the table alphabetically. I've looked long and hard and haven't been able to quite find a solution to this around this forum or various others.

Finally, using both of those dlookup expressions, i've created the following IIF expression, which works, but only kind of because of the 'branch manager' thing not working:
Manager: IIf([managingrole]="Branch Manager",DLookUp("[qryBranchManagers]![ProperName]","qryBranchManagers"," [qryBranchManagers]![Role]='" & [ManagingRole] & "'" And "[qryBranchManagers]![Location] ='" & [Location] & "'"),DLookUp("[qryManagement]![ProperName]","qryManagement"," [qryManagement]![RoleName] ='" & [ManagingRole] & "'"))

Perhaps its something simple, perhaps there's an easier way of doing this that i've overlooked? I'd appreciate any help someone can provide, and thank you in advance!

 

[Minty]

That's a very detailed description of your problem, but what I would really like to see is some sample data (Remove personal data):
Two sets of data 1)your source data, 2) Your expected output .

I can't visualise the 2 data sets from your explanation I'm afraid.
Replace John Smith with Micky Mouse if it helps.

I suspect you can do what you want with a sub query, rather than a DLookup.

 

[Gasman]

Do yourself a favour.
When using domain functions like DLookUp(), do not prefix fields with domain name, that is what the domain parameter is for.?
Easy to code and easier to see.

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the function.
Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[jpjordan357]

That's a very detailed description of your problem, but what I would really like to see is some sample data (Remove personal data):
Two sets of data 1)your source data, 2) Your expected output .

I can't visualise the 2 data sets from your explanation I'm afraid.
Replace John Smith with Micky Mouse if it helps.

I suspect you can do what you want with a sub query, rather than a DLookup.

Hi Minty, see if this works:

I want my 'Manager' field to fill in based on the managing role. If the 'managing role' is 'branch manager', then the Manager field must fill in with the name of the person that has been assigned as a branch manager at the same location. So in the above example. CEO Les Nessman has Herb Tarlek and Andy Travis reporting to him, regardless of location because their 'Managing Role' is literally anything other than 'Branch Manager'. However, Johnny Fever, Baily Quarters, and Jennifer Marlowe all have 'ManagingRole' set to 'Branch Manager', so they 'Manager' field needs to fill in with the branch manager at their location. In this case, Johnny and Baily's 'Manager' field should fill in with Venus Flytrap, since he's the 'Branch Manager' at the Akron location, and Jennifer should fill in as Arthur Carlson because he's the 'Branch Manager' at the Cleveland location.

Does that help?

 

[jpjordan357]

Do yourself a favour.
When using domain functions like DLookUp(), do not prefix fields with domain name, that is what the domain parameter is for.?
Easy to code and easier to see.

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the function.
Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

I appreciate the feedback. I literally just started putzing with Dlookup yesterday, and i'm not sure that I have for over a decade since, so don't have any idea what you're saying, regarding the string variable. I left the domain name prefix on there mostly for my benefit, since I'm still learning how to make an expression with proper syntax.

 

[Pat Hartman]

I would rethink the design. I understand the reluctance to use a FK to the actual person to find the manager so instead, I would use department or some similar concept where department is unique. Then in the department table, you would use a FK to the actual manager and another FK to the manager's title. If your business rules allow a department to be leaderless, then the EmployeeID in the department table can be defined as optional with a null value rather than required. You will need to accommodate this in your queries by using a left join from department to Employee.

But, going with a FK to the actual employee who is the supervisor is the common solution. When you replace a supervisor, you simply run an update query to populate the new FK by selecting based on the old supervisor's PK and updating to the new supervisor's PK. You still have the decision as to whether a group of people can be leaderless or not. I would go with Not and just name one member of the group as "acting" until a permanent supervisor can be assigned.

 

[jpjordan357]

I would rethink the design. I understand the reluctance to use a FK to the actual person to find the manager so instead, I would use department or some similar concept where department is unique. Then in the department table, you would use a FK to the actual manager and another FK to the manager's title. If your business rules allow a department to be leaderless, then the EmployeeID in the department table can be defined as optional with a null value rather than required. You will need to accommodate this in your queries by using a left join from department to Employee.

I'm definitely open to a design change, however, I'm afraid i don't know how to do anything of what you just said. I go so long between database creation that I have to relearn things as I do them; and this is a bit more complex than I've ever made up before. That said, the role-based scheme we got going on here was to accommodate a different function; i was asked to add this as another tool after the fact. If I could somehow have that Branch Manager role as a master for however many branch mangers I have, such that if I have 9 separate locations that have branch managers, and a record for each, I would want permissions that i assigned to the Branch Manager role to automatically update the other 9, if that makes sense. Or is that not where you were going with that?

 

[Minty]

I think I understand a little better now. So in basic logic terms

If ManagerRole = BranchManager Then Employee Manager = Location BranchManager ?

This can be done easily but still leaves your other roles needing filling in manually.
I think I would go with a version of Pat's proposed solution, simply store the EmpId of the manager in a Manager_ID field.

You can run an update based on the above logic as a one-off to populate it's value to start off, and if a manager changes simply update all those records.

 

[Gasman]

I appreciate the feedback. I literally just started putzing with Dlookup yesterday, and i'm not sure that I have for over a decade since, so don't have any idea what you're saying, regarding the string variable. I left the domain name prefix on there mostly for my benefit, since I'm still learning how to make an expression with proper syntax.

You only need the [] if you have spaces in field/control names (not a good idea)

Dim strCriteria as String

strCriteria = "Role='" & ManagingRole & "'" And "Location ='" & Location & "'"
Debug.Print strCriteria

DLookUp("ProperName","qryBranchManagers"," strCriteria)

If they were form controls then I would use

Dim strCriteria as String

strCriteria = "Role='" & Me.ManagingRole & "'" And "Location ='" & Me.Location & "'"
Debug.Print strCriteria

DLookUp("ProperName","qryBranchManagers"," strCriteria)

 

[jpjordan357]

I think I understand a little better now. So in basic logic terms

If ManagerRole = BranchManager Then Employee Manager = Location BranchManager ?

This can be done easily but still leaves your other roles needing filling in manually.
I think I would go with a version of Pat's proposed solution, simply store the EmpId of the manager in a Manager_ID field.

You can run an update based on the above logic as a one-off to populate it's value to start off, and if a manager changes simply update all those records.

Is there an expression that captures the above logic statement?

 

[jpjordan357]

You only need the [] if you have spaces in field/control names (not a good idea)

Dim strCriteria as String

strCriteria = "Role='" & ManagingRole & "'" And "Location ='" & Location & "'"
Debug.Print strCriteria

DLookUp("ProperName","qryBranchManagers"," strCriteria)

If they were form controls then I would use

Dim strCriteria as String

strCriteria = "Role='" & Me.ManagingRole & "'" And "Location ='" & Me.Location & "'"
Debug.Print strCriteria

DLookUp("ProperName","qryBranchManagers"," strCriteria)

Any way to plug that into an expression, or otherwise use it in a query?