UtterAccess.com
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    
 
   
mmai
post Sep 21 2019, 01:16 PM
Post#1



Posts: 9
Joined: 21-September 19



Hi all,
I need a function that will generate a "newusername" value for the username listed in TableCalculated, that will not conflict with any username value that exists in Table1 or Table2. The function needs to increment the generated newusername value by a number (consecutively) until no conflict with any values in Table1 or Table2.

Example:
Table1/username field has values: John, John1, John2, John3
Table2/username has values: John, John2

TableCalculated/username has value: john
TableCalculated/newUsername= (need your help with this function!). The value the function should return is John4 (Since John4 will not conflict with any value in Table1 nor Table2)
Go to the top of the page
 
GroverParkGeorge
post Sep 21 2019, 01:18 PM
Post#2


UA Admin
Posts: 35,885
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

What have you tried so far? What problems did you encounter? What specific issues can we give you instructions or suggestions for.

Thanks

--------------------
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
 
mmai
post Sep 21 2019, 01:27 PM
Post#3



Posts: 9
Joined: 21-September 19



Hi, I haven't tried any code yet. I am able to create queries that will show me which usernames from TableCalculated have a match (thus conflict) with usernames in Table 1 and Table2, but I don't know how to create this function at all sadly. Does my function requirement sound do able?
Go to the top of the page
 
jleach
post Sep 21 2019, 01:32 PM
Post#4


UtterAccess Editor
Posts: 10,146
Joined: 7-December 09
From: St Augustine, FL


Hi,

It's a two-part thing, I think. The first would be to generate a probable username, the second to validate it.

I'm not 100% sure what your username generation code might look like, but let's assume its the first name, appended with a number if need be. If so, we can run a loop to add a number then try again. Something like this (aircode)

CODE
Dim i as Integer
Dim Username as String

Username = "jack"

If VerifyUsername(username) = False then
   For i = 1 to 100 ' (or whatever max)
   Username = username & trim(str(i))
   If VerifyUsername(username) then
      Goto Exit_Proc
   End If
End If


Function VerifyUsername(username As String)
  Dim test as String
  test = nz(dlookup("usernamefield", "firsttable" "username = """ & username & """"), "")
  test = test & nz(dlookup("usernamefield", "secondtable" "username = """ & username & """"), "")

  If test = "" then VerifyUsername = True



Obviously the code is a mess (and use a while instead of a for loop), but that's the basic idea.

hope it helps,

--------------------
Go to the top of the page
 
theDBguy
post Sep 21 2019, 01:34 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,393
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

I’m curious why you have two tables with usernames.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jleach
post Sep 21 2019, 01:34 PM
Post#6


UtterAccess Editor
Posts: 10,146
Joined: 7-December 09
From: St Augustine, FL


I should add: you should only have to validate this against one table. There should be a single source of truth (e.g., one table) that will house all of the username definitions in the database.

That you have to validate this against two fields/tables instead of one is a strong indicator of an improperly normalized data structure. Might be worth looking into (normalization: there's a lot of topics on the concept if you're not familiar with it).

Good luck!

(ha! DBGuy beat me to it by seconds!)

--------------------
Go to the top of the page
 
mmai
post Sep 21 2019, 01:42 PM
Post#7



Posts: 9
Joined: 21-September 19



I have two tables of usernames because the each one is linked to a different source. (Table1 is linked to table data in SQL server that represents usernames from Application1). Table2 is linked to the same SQL server but a different table that represents usernames from Application2.

I'll try to code you supplied - I assume if it finds no conflict, then it will just re-use the existing username?

Do you think it's better that I first run some kind of query to combine all usernames from Table1 and Table2 , into maybe Table-Normalized, and then this would simplify things in terms of code?
Go to the top of the page
 
nvogel
post Sep 21 2019, 02:11 PM
Post#8



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


If your data is in SQL Server then why not generate the user name in SQL Server? SQL Server has a feature that will help you do this: SEQUENCE. Sequences are independent of tables and you can increment the sequence in T-SQL code each time you want a new user name.
Go to the top of the page
 
mmai
post Sep 21 2019, 02:22 PM
Post#9



Posts: 9
Joined: 21-September 19



I am not the SQL admin and sadly I don't know SQL language very well, that's why Access has been helpful for me. On another note, I'm trying the vba code supplied here but not able to make it work
Go to the top of the page
 
nvogel
post Sep 21 2019, 02:37 PM
Post#10



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


Maybe you could ask someone else to help you. SQL Server makes probably more sense for this than VBA. Below is some code that might help you do it in SQL Server.

I do agree with the other suggestions that creating one table is better than two. Maybe you could replace one of those tables with a synonym that references the other table.

INSERT INTO users (username) VALUES (@username);
WHILE @@ROWCOUNT=0
BEGIN;
SET @inc = NEXT VALUE FOR username_seq;
/* predefined sequence: CREATE SEQUENCE username_seq START WITH 1 INCREMENT BY 1; */
INSERT INTO users (username) VALUES (LEFT(@username,20-LEN(@inc))+@inc);
END;

This code is written on the assumption that username has a unique constraint on it.


This post has been edited by nvogel: Sep 21 2019, 02:49 PM
Go to the top of the page
 
mmai
post Sep 21 2019, 02:56 PM
Post#11



Posts: 9
Joined: 21-September 19



I was able to generate a "Normalized" table of all username values combined from Table1 and Table2. So now I just need the VBA code which will generate a unique username by comparing if the value exists "Normalized", then increment by one digit.

Example:

Table_Normalized has Username field values of John, John1, John2, John3, John4
Table_NewUser has username field value of John.

I need to generate "new_username" field, in table "newuser" which will check if the existing field value "john" exists in table Normalized. If not, make no change to existing value. If exists, then use next available value (which in this example is John5)
Go to the top of the page
 
Jeff B.
post Sep 21 2019, 06:22 PM
Post#12


UtterAccess VIP
Posts: 10,304
Joined: 30-April 10
From: Pacific NorthWet


I'm not sure I'm visualizing what you consider a "username". Could you provide a few examples?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
mmai
post Sep 21 2019, 06:27 PM
Post#13



Posts: 9
Joined: 21-September 19



Sure, I was simplifying usernames like John, John1, John2. But the usernames in reality are First Initial, Last Name. So "jsmith", "jsmith2", etc.. All of the usernames in-use are in the table called "normalize". And for each user in my "new users" table, they already have a generated username of first-initial last name, but if it conflicts with something in the normalized table, then I need some expression which will generate the new username value and increment it by 1 digit. So new user John Smith, with predefined username of jsmith, will get jsmith3, since jsmith and jsmith2 are already taken.
Go to the top of the page
 
Jeff B.
post Sep 21 2019, 06:36 PM
Post#14


UtterAccess VIP
Posts: 10,304
Joined: 30-April 10
From: Pacific NorthWet


So it appears that Jim Smith would also get a "jsmith#" assigned, right? … as would Julia Smith, and …

Have you considered using the built-in Autonumber data type to have Access generate a unique identifier that doesn't rely on the person's names/initials? After all, what is the purpose of the "username"? That is, what will having it (and having it be unique) then allow you/your users/Access to do?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
June7
post Sep 21 2019, 06:55 PM
Post#15



Posts: 875
Joined: 25-January 16



Cross post https://access-programmers.co.UK/forums/sho...ad.php?t=306968

--------------------
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
 
tina t
post Sep 21 2019, 07:17 PM
Post#16



Posts: 6,120
Joined: 11-November 10
From: SoCal, USA


QUOTE
And for each user in my "new users" table, they already have a generated username of first-initial last name, but if it conflicts with something in the normalized table, then I need some expression which will generate the new username value and increment it by 1 digit.

well, 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.

but, if the username is stored as a single value in one field, you'll need to isolate the number value at the end of the text string, and increment that by 1, and then concatenate it with the two existing name values to create a new username to be assigned to the new user record.

as general suggestions (not knowing anything in detail about your setup or how/where you want to implement generation of the new username), i'd say take a look at the Mid() function and the Len() function, to construct an expression that would take the combination of first initial (or first character of first name) and last name, get the string's length, then get the character(s) following, in existing user records. having that, you can use the Max() function (or Dmax()) to get the highest isolated number. then add 1 to that number, and concatenate the new user's name values and the new number together and save into the new user record. i imagine you could do this in either SQL or VBA.

hth
tina


--------------------
"the wheel never stops turning"
Go to the top of the page
 
mmai
post Sep 21 2019, 08:14 PM
Post#17



Posts: 9
Joined: 21-September 19



the user will use that their generated username to log into the company network eventually, so I'm not looking for a built in autonumber/unique identifier. Yes, these usernames are somewhat considered unique identifiers, but not until they are in the format we need.

And to reply to Tina, yes we have usernames that already exist as Jsmith1 or Jsmith2 so the code does need to isolate the value at the end if it finds such a match and produce a username with number field that has no conflict.

Thought this was easier than it's looking. I'm feeling like this might be impossible
Go to the top of the page
 
mmai
post Sep 21 2019, 08:21 PM
Post#18



Posts: 9
Joined: 21-September 19



And also replying to Tina, maybe what I need to do is create some kind of split calculation that will take the existing usernames as they exist in the master table, and split them when a number is found ? Such as, record jsmith1 will split into two columns: jsmith and 1 . Then once all of the existing usernames are split like this, then I query the max number somehow? But i'm not sure how to even go about splitting the existing usernames (though it does sound doable) and how to call the max number query to generate the end result
Go to the top of the page
 
June7
post Sep 21 2019, 08:25 PM
Post#19



Posts: 875
Joined: 25-January 16



May be over-thinking problem. As suggested in other forum, use a combobox.


--------------------
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
 
mmai
post Sep 21 2019, 08:35 PM
Post#20



Posts: 9
Joined: 21-September 19



a combo box of what values?
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 02:06 PM