Lookup and assign value based upon text within alpha range

[jco23]

the users of my app would like to auto-assign someone to a task based upon the first two letters of a company name. I currently have a table with each letter of the english alphabet (including numbers 0-9) and the corresponding assignee. for example:
A: Stan
B: Kyle
C: Timmy
D. Cartman
E. Kenny
F. Stan
G: Timmy
H: Timmy

my logic is basically whenever a new task comes in, the app will lookup the first character of the company associated with that task (i.e. G for Google, A for Apple, 1 for 1-800-Flowers, etc.) in that table.

However, due to an increase in employees, they are asking to split out some of the alphas (i.e. A through AMZ will go to Stan while AN through AZZ will go to Kyle).

Is there a way to can get the logic to identify which person to assign the task to based upon a an alpha range?

thanks!

 

[CJ_London]

If you are currently basing on the first character what is stopping you basing on the first two characters? If this is an exception just for those companies start with A that would be AA-AM then AN-AZ. Companies starting with B would be BA-BZ, etc

You would need a range comparison e.g.

Where left(compname,2) between assignees.minchar and assignees.maxchar

 

[GPGeorge]

the users of my app would like to auto-assign someone to a task based upon the first two letters of a company name. I currently have a table with each letter of the english alphabet (including numbers 0-9) and the corresponding assignee. for example:
A: Stan
B: Kyle
C: Timmy
D. Cartman
E. Kenny
F. Stan
G: Timmy
H: Timmy

my logic is basically whenever a new task comes in, the app will lookup the first character of the company associated with that task (i.e. G for Google, A for Apple, 1 for 1-800-Flowers, etc.) in that table.

However, due to an increase in employees, they are asking to split out some of the alphas (i.e. A through AMZ will go to Stan while AN through AZZ will go to Kyle).

Is there a way to can get the logic to identify which person to assign the task to based upon a an alpha range?

thanks!

It seems to me that the most straightforward way will be to revise and expand your current table.
Convert the single character lookup values to 3 character lookup values and repopulate the assignments according to your new rules.

Once the conversion is in place, in fact, you'll only need to add additional splits to further break down the assignments when growth continues and you need further subcategories.

In other words, A becomes "AAA -- AMZ" and "ANA -- AZZ"

 

[jco23]

thanks for the quick responses, CJ_London and GPGeorge!

I suppose I should also mention that my current lookup method is just a dlookup:

Dim companyF As String

companyF = Left([Company_Name], 1)

Me.Assigned_To = DLookup("[Analyst]", "[tbl_company_analysts]", "[first_char] = '" & companyF & "'")

I can adjust the values in the table to anything, but what VBA do I use to determine which analyst would get a company like "Allegro" ?

should I no longer use dlookup for this?

 

[CJ_London]

You can still use a dlookup - just modify the where parameter along the lines of my example - having first added and populated your min/max char fields.

You mentioned 3 chars but your example was for two

 

[The_Doc_Man]

If you want three-character lookups, use LEFT(original-string, 3) instead of LEFT(original-string,1)

 

[XPS35]

Like this?

Dim companyF As String

companyF = Left([Company_Name], 2)
Me.Assigned_To = DLookup("[Analyst]", "[tbl_company_analysts]", "[first2_char] = '" & companyF & "'")

 

[Pat Hartman]

I can adjust the values in the table to anything, but what VBA do I use to determine which analyst would get a company like "Allegro" ?

What is your new rule going to be? You should also look at the distribution of companies by first letter or first two letters so you can determine if the buckets are approximately equal. Otherwise some people will be assigned more companies than others.

If you assign a range to each person, you need two fields rather than one. Then you can use Between FirstLetters and SecondLetters.

Also, you have to be very careful to not leave any gaps or an analyst won't get assigned so you need to check the result of Me.Assigned_To to ensure it is not null or "" and have a default analyst you can plug in.

 

[GPGeorge]

thanks for the quick responses, CJ_London and GPGeorge!

I suppose I should also mention that my current lookup method is just a dlookup:

Dim companyF As String

companyF = Left([Company_Name], 1)

Me.Assigned_To = DLookup("[Analyst]", "[tbl_company_analysts]", "[first_char] = '" & companyF & "'")

I can adjust the values in the table to anything, but what VBA do I use to determine which analyst would get a company like "Allegro" ?

should I no longer use dlookup for this?

Well, more context almost always leads to more complete answers.

So, you are currently basing the lookup on one character. You could expand that to three characters, or two, or five.
CompanyF = Left([Company_Name],3)

Now you have to do a range based Lookup for the starting and ending segments.
Actually, I was too hasty before. The Assignment table will need three fields:

StartRange	EndRange	AnalystAssigned
AAA	AMZ	Curly
ANA	AZZ	Moe
BAA	BZZ	Larry

Allegro would yield "ALL" ( I would convert to all caps or all lower case).

So the lookup becomes something along the lines of:

Me.Assigned_To = DLookup("Analyst", "[tbl_company_analysts]", "[Start_Range]" <= '" & companyF & "'" AND [EndRange] >= '" & companyF & "'" )

 

[The_Doc_Man]

Or ... SLIGHTLY less typing

Me.Assigned_To = DLookup("Analyst", "[tbl_company_analysts]", "'" & companyF & "' BETWEEN [StartRange] AND [EndRange]" )

With the understanding that this ONLY works correctly when the text ranges are all exactly the same length in text characters as well as being all uppercase; and companyF has to ALSO be uppercased as well as extracted: UCASE( LEFT( company-name, 3 )) - if you are using 3-character ranges.

 

[MarkK]

Wouldn't it make more sense--and be easier to accomplish--if the next incoming task is assigned to the person with the fewest tasks?

 

[Pat Hartman]

Wouldn't it make more sense--and be easier to accomplish--if the next incoming task is assigned to the person with the fewest tasks?

Why would you ever bring common sense into such a convoluted discussion

If you have ties, it doesn't matter who gets the next customer. If it does to your client, then you might keep track of the date the last customer was assigned if you want to continue with slightly different complexity.

 

[The_Doc_Man]

While there are queueing theory solutions to optimize performance based on who has the least load, the idea of directing specific companies to specific representatives also makes sense if your goal is to form a predictable relationship. If "Fred" always gets a certain set of customers, he builds a trust relationship with those folks while "Eric" gets another set.. One of my college professors in computer science used to work for IBM back in the 1960s. They did their best to to maintain continuity of business contacts when handing out work assignments. Customer relations can drive all sorts of business choices.

 

[arnelgp]

another possibility is do a a simple rotation of employees.
see AssignmentForm and see the code behind it.
this will give "balance" assignment and everybody gots
equal chance to get assigned.

 

[jco23]

Wouldn't it make more sense--and be easier to accomplish--if the next incoming task is assigned to the person with the fewest tasks?

yeah, I've suggested that, but they want certain folks focused on specific companies as some are more complex than others.

 

[jco23]

Well, more context almost always leads to more complete answers.

So, you are currently basing the lookup on one character. You could expand that to three characters, or two, or five.
CompanyF = Left([Company_Name],3)

Now you have to do a range based Lookup for the starting and ending segments.
Actually, I was too hasty before. The Assignment table will need three fields:

StartRange	EndRange	AnalystAssigned
AAA	AMZ	Curly
ANA	AZZ	Moe
BAA	BZZ	Larry

Allegro would yield "ALL" ( I would convert to all caps or all lower case).

So the lookup becomes something along the lines of:

Me.Assigned_To = DLookup("Analyst", "[tbl_company_analysts]", "[Start_Range]" <= '" & companyF & "'" AND [EndRange] >= '" & companyF & "'" )

I'm limited to just two characters for the start range, but I can make this work.
FYI, I had to remove two double quotes from your code (the one after [start_range] and the one before AND). but after making that adjustment, I got it to work as expected.
this was very helpful, thank you!

 

[jco23]

another possibility is do a a simple rotation of employees.
see AssignmentForm and see the code behind it.
this will give "balance" assignment and everybody gots
equal chance to get assigned.

this is helpful, but I already got something similar in another portion of my app. thanks!

 

[jco23]

Or ... SLIGHTLY less typing

Me.Assigned_To = DLookup("Analyst", "[tbl_company_analysts]", "'" & companyF & "' BETWEEN [StartRange] AND [EndRange]" )

With the understanding that this ONLY works correctly when the text ranges are all exactly the same length in text characters as well as being all uppercase; and companyF has to ALSO be uppercased as well as extracted: UCASE( LEFT( company-name, 3 )) - if you are using 3-character ranges.

good suggestions - i'll make sure to do that. thank you!

 

[arnelgp]

as suggested by the others, you can create a new table (junction table) that will hold the companyID and assigned
employee to it (tblEmpCompanies).

see form CompanyEmployeeForm to see which employee is assigned to which customer.

open AssignmentForm form for your assignment and see the company combobox AfterUpdate event.

 

[GPGeorge]

I'm limited to just two characters for the start range, but I can make this work.
FYI, I had to remove two double quotes from your code (the one after [start_range] and the one before AND). but after making that adjustment, I got it to work as expected.
this was very helpful, thank you!

No problem, really, two characters is the same logic.

I apologize for the bonus quotes, typing code in a vacuum isn't my strongest skill.