X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Function That Generates A Username Value With No Conflict In Other Tables, Access 2016    
post Sep 21 2019, 08:48 PM

Posts: 967
Joined: 25-January 16

Read your thread in other forum for details.

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post Sep 21 2019, 10:28 PM

UA Admin
Posts: 36,043
Joined: 20-June 02
From: Newcastle, WA

I think tina hit the nail on the head.

"...here's where we see the reason for the normalization rule against combining multiple bits of data into one field. if the table has a first initial (or first name) field, and a last name field, then all it really needs to generate a unique username is a field to hold the "number". having that field, it would be quite easy to query out the first initial/last name and max number, add one to the number, and store it back into the new user's record. to use a "complete" username anywhere in the db, it would be simple to concatenate those three fields together."

Sometimes we get lost in the challenge of "solving a logic problem" with VBA and SQL, when the more effective solution is simply to rely on the tried and true Rules of Normalization.

tina's solution is the most appropriate one I can think of and the EASIEST to implement properly.

Unfortunately, it sounds like you have historical data and that's going to require some data clean up. But once that's completed, the task is much more straightforward after that.

I'd start by cloning the existing table, but in the process create the three fields mentioned: FirstName, LastName, UniqueCounter (or whatever you decide to call the field that has the incrementing value).

To get the value for the FirstName field either populate it from your existing FirstName field or by parsing the existing username: It would be Left([YourUserNameFieldGoesHere],1), assuming that you've been consistent in assigning user names from the First Initial only.

To get the last name, you'll need to do some additional calculations to find only the alpha characters following that initial letter from the first name. I would hope that your Master table actually does have people's first and last names, though. In other words, if you only have stored these calculated usernames and have, therefore, lost the ability to identify people by their real first and last names, you run headlong into the problem Jeff raised: JSmith could be Jack Smith, John Smith, Jessica Smith, and so on. Of course they are differentiated by the code numbers but there wouldn't be any way to know, would there, whether JSmith1 is Jack, John or Jessica. Or JSmith2, or JSmith3? Users fade into anonymity without the source values used in the calculated values.

Perhaps a good way to get to a solution more quickly would be to provide a sample of the database for review. Sometimes, looking directly at the raw tables and the data in them makes it possible to understand problems and offer better suggestions.


My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Sep 22 2019, 07:18 AM

Posts: 1,023
Joined: 26-January 14
From: London, UK

Another perspective however is that it's quite reasonable to store a unique login name (an important value in its own right) independently and in addition to any surname, first name and initial. Including numbers in a login name doesn't alter the requirement to store a login name and after all some people might want to modify their login name to remove any numbers because words are easier to remember or just to anonymize it. Also the login may some day need to be obfuscated/pseudonymized for regulatory or legal reasons.

It isn't "against the rules" of normalization to store a login name in one column any more than it would be to store an email address or URL that way. Atomicity is always very subjective and a lot depends on how you intend to use the value in question. A login name I would suggest is of very little use as separate components and splitting it up could even be detrimental because you can't properly enforce its uniqueness with a composite key alone. For example the two tuples, ("joe23","4") and ("joe2","34"), would presumably represent the same login.

Just my thoughts.

This post has been edited by nvogel: Sep 22 2019, 07:19 AM
Go to the top of the page
2 Pages V < 1 2

Custom Search

RSSSearch   Top   Lo-Fi    17th November 2019 - 12:44 AM