Lookup and assign value based upon text within alpha range

[GPGeorge]

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.

Interestingly enough, I've been so conditioned not to use BETWEEN because of the problem of Dates with times. It is appropriate here and is simpler syntax.

 

[Gasman]

Just throw a DateValue() at them?

 

[The_Doc_Man]

Interestingly enough, I've been so conditioned not to use BETWEEN because of the problem of Dates with times. It is appropriate here and is simpler syntax.

Probably works for this case because the reference ranges are likely to not include times, in which case you don't care much if the tested date field or variable does or doesn't have time in it. If the reference points had times, then you get into nit-picking with BETWEEN. BUT it wouldn't hurt to trim time out of the tested date field just to be sure. That same caveat would have applied to the other syntax with <= and >= components.

 

[GPGeorge]

I was thinking it works here because the reference values are discrete alpha characters....

 

[Gasman]

My thoughts too.

 

[jco23]

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.

ideally, that would work, but users have gotten in the habit of using short names while others are using long names (e.g. IBM vs. I.B.M vs. International Business Machines vs. Int' Bus. Machines). we're in the process of cleaning some of that up, but this method works. this group deals with hundreds of thousands of transaction each month, so fixing everything could send us back too far.

 

[arnelgp]

i think that you have Standard tables for your Employee's right? so misspelling them on data entry is not acceptable.
you also Standardized the naming of your clients/companies. So when it is time to Filter your Transactions/Assignments
would be very easy and mistake will be avoided.

, so fixing everything could send us back too far.

you devout a special time for this. Fixing it now will save you a lot of headaches later.

 

[Pat Hartman]

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

Maybe companies can be assigned a complexity type or perhaps an industry type. Use something that makes sense as a grouping and then you can have employees assigned to a group and go from there.

 

[gemma-the-husky]

If you aren't careful someone will draw the short straw and get more than his fair share of work, using an alphabetical formula like that. I expect there are a lot of names starting with B for instance.

 

[MajP]

If you aren't careful someone will draw the short straw and get more than his fair share of work, using an alphabetical formula like that. I expect there are a lot of names starting with B for instance

My thought is if you really had to do this assignment by names then you can use your current data to get the best break points. This assumes if you have a large distribution of names that future names would follow that distribution.
1. Determine how many buckets you want. Maybe that is one employee per bucket
2. Read the current names and break the data into buckets and use that info to set your range.

I have 10k names. The demo uses people names, but pretend those are companies. To generate your buckets.

Public Sub CreateRanges()
  Const TableName = "TblCompanies"
  Const FieldName = "CompanyName"
  Const NumberLetters = 6
 
  Dim Buckets As Integer
  Dim BucketSize As Long
  Dim rs As DAO.Recordset
  Dim RangeStart As String
  Dim RangeEnd As String
  Dim strSql As String
  Dim i As Long
   Set rs = CurrentDb.OpenRecordset("Select " & FieldName & " FROM " & TableName & " ORDER BY " & FieldName)
  Buckets = GetBuckets  'could be a count of employees
  Debug.Print Buckets
  If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    BucketSize = rs.RecordCount / Buckets
    RangeStart = Left(rs.Fields(FieldName), NumberLetters)
    For i = 1 To Buckets - 1
       rs.Move BucketSize
      RangeEnd = Left(rs.Fields(FieldName), NumberLetters)
      strSql = "Insert INTO tblRanges (RangeStart, RangeEnd) VALUES ('" & RangeStart & "','" & RangeEnd & "')"
      CurrentDb.Execute strSql
      RangeStart = RangeEnd
    Next i
    rs.MoveLast
    RangeEnd = Left(rs.Fields(FieldName), NumberLetters)
    strSql = "Insert INTO tblRanges (RangeStart, RangeEnd) VALUES ('" & RangeStart & "','" & RangeEnd & "')"
    CurrentDb.Execute strSql
    Debug.Print strSql
  End If
End Sub

This creates this table that then needs to get edited. Assuming I have 20 employees

RangeStart	RangeEnd
Aaliya	Amalia
Amalia	Beatri
Beatri	Carley
Carley	Consue
Consue	Dessie
Dessie	Eloise
Eloise	Flossi
Flossi	Harmon
Harmon	Janick
Janick	Joy Ge
Joy Ge	Kim Ge
Kim Ge	Lionel
Lionel	Margue
Margue	Miss A
Miss A	Nathen
Nathen	Pete G
Pete G	Rosale
Rosale	Stanle
Stanle	Vallie
Vallie	Zul Wi

tblRanges

To make sure no future records fall between the gaps you need to edit the range start by increase the last letter of the Rangestart by one letter, and extend the boundaries of the first and last record. Amailia becomes Amailib

RangeID	RangeStart	RangeEnd
222	aaaaaa	Amalia
223	Amalib	Beatri
224	Beatrj	Carley
225	Carlez	Consue
226	Consuf	Dessie
227	Dessif	Eloise
228	Eloisf	Flossi
229	Flossj	Harmon
230	Harmoo	Janick
231	Janicl	Joy Ge
232	Joy Gf	Kim Ge
233	Kim Gf	Lionel
234	Lionem	Margue
235	Marguf	Miss A
236	Miss b	Nathen
237	Natheo	Pete G
238	Pete H	Rosale
239	Rosalf	Stanle
240	Stanlf	Vallie
241	Vallif	zzzzzz

I did not account for numeric names in this example or capitals. I would convert everything to upper or lower case in the query. As you get more names or more employees you can rerun the code and reset your buckets.

 

[Pat Hartman]

Jet/ACE are not case sensitive by default but SQL Server can be if you choose. But case just adds complexity that you don't need so follow the advice to force lower or upper in all cases.

You can add a couple of numeric ranges if you find you need them ... 000-499 and 500-999 should be fine.

 

[Isaac]

It's a good lesson in creating things that scale, even (and perhaps especially) when it seems like they won't need to.

At my current company, we have a table called tbl_PatientOrder. One database per client in our transactional system.
They are running out of INT values on the ID column, and let me tell you, 15 years down the road and 15 BILLION bits of code from 10-20 different coding platforms later, it's NOT FUN to be involved in the project of converting INT to BIGINT. It's a large project involving 20+ people, all development teams, and that's just to scope it out and begin trying to assess what it will impact. The impact is little in many cases, but they all have to be fastidiously checked to be sure. Literally the project was so daunting because of the wide scope of impact, that they decided to start assigning INT values from the bottom just to buy us another year or so

The original developer was apparently not very optimistic about company growth.