Access Pacific Monthly Meeting (Codifying Long Names into unique codes with Jack Stockton)

Please join us for our Monthly Chapter Presentation on Sept 1st, at 6:30PM Pacific (UTC -8)

As always, double-check the UTC time zone offsets because Daylight Savings Time plays havoc with that calculation.

Access Pacific holds FREE Q&A webinars the First Thursday of every month, from 6:30 pm to 7:30 pm Pacific.

==============================================================================

Jack Stockton will share his variation on a method to codify names into unique, 2 to 5 character codes to improve usability of combo and list boxes and other places where users don’t need to see the full phrase.

Jack explained, “Recently, Mike Wolfe posted about creating a Codifying T-SQL function for SQL server,  https://nolongerset.com/sql-server-codify-function.  I found the concept very interesting, but didn’t think it really needed to be a T-SQL function.  I might not have a SQL backend, but could use the feature.  So what does Codifying do. Mike says “This function will jump-start the process of converting long descriptions into meaningful abbreviations. It’s great for creating “Code” columns in lookup tables, 2- to 5-character abbreviation”.”

With Jack’s Access version of Codify, he added the base requirements:

  • Code Length 2 to 5 characters returned
  • Use the first letter from each word
  • Replace instances of ‘and’ with ‘&’
  • Don’t allow a trailing ‘&’

Added features

  • If only one word, return first Code Length letters in the word
  • Upper case returned
  • Only Alpha character returned
  • Use any separator character, not just a space

Example output

industry Codify2 Codify3 Codify4 Codify5
General Merchandisers GM GM GM GM
Petroleum Refining PR PR PR PR
Insurance: Property and Casualty (Stock) IP IPC IP&C IP&CS
Computers, Office Equipment CO COE COE COE
Motor Vehicles and Parts MV MVP MV&P MV&P
Diversified Financials DF DF DF DF
Food and Drug Stores FD F&D F&DS F&DS

This particular function appealed to me especially because it reflects one of the design discussions the Northwind 2 Working Team recently had regarding exactly this topic. I suspect something like this will find its way into the new version of Northwind.

If we have time, Jack will also demo a function for generate a repeatable 32 character Unique string based on a input string.  Often there can be a needed repeatable process when importing records from different sources or dumping and reloading the same data. This makes that easier and quicker.

Please prepare ahead of time by downloading Zoom, if you don’t already have it.

Get Zoom

To join our meetings, please use the following link and phone number or use your computer’s speakers with audio:

When it’s time, join the meeting from here:

Join Zoom Meeting

If you are asked:

Meeting ID: 861 2395 1916
Passcode: AUG_AP1!

Dial by your location
+1 253 215 8782 US (Tacoma)
+1 346 248 7799 US (Houston)
+1 669 900 9128 US (San Jose)
+1 301 715 8592 US (Washington DC)
+1 312 626 6799 US (Chicago)
+1 646 558 8656 US (New York)
Meeting ID: 861 2395 1916
Passcode: AUG_AP1!
Find your local number