Lookup and assign value based upon text within alpha range

[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.

 

[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.

 

[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.

 

[Isaac]

Why do I find that concept terrifying?

Sounds similar to the Y2K problem which was also trivial on the surface but a real PITA to fix. It would have been much simpler if people had been consistent with using copy books for record layouts. Then you would only have had to modify the copy book and then compile all the programs that used it. You wouldn't have needed to modify every program individually.

Same thing for fixing the leap year calculation. Calling common code would have made the fix infinitely easier but no, everyone wrote their own code and many didn't know all the rules which is how we got there to begin with. Every one knows the 4 year rule and most know the 100 year rule but very few ever got to the 400 year rule.

I'm sure there's a bunch of companies still fighting with the SSN change. If you defined SSN as numeric instead of text as you should have, that one bit you a few years ago when the SSA started issuing replacement cards that included letters as well as numbers.

We are subject to various laws, rules and regulations which require a certain separation of data, it's all a complicated part of the contracts signed, etc. etc. etc.

But that's just our transactional system., or "OLTP" as some might say. It has to remain lean and fast per client.

We also have a data warehouse that combines them for doing broader analysis and for reporting in general.

And all client DBs have the identical structure.

I can say that we make good use of Multiscript, a tool where you can bring up a distro list of db's and servers, check certain ones off, and execute your sql script on them all

 

[Isaac]

that one bit you a few years ago when the SSA started issuing replacement cards that included letters as well as numbers.

Wow, I did not know that.

Yeah .. they should have asked themselves the question, "Will we ever perform MATH on a SSN? If not, it's text"