How to assign unique, sequential user IDs (like B-102)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to get my db to auto generate a unique user ID based on first letter
of last name and the number (say, Don Baker is first B, so he gets B-0001).
Is there a way to do this without manually assigning myself? Thanks.
 
Use a query to find Max(ID) for Left(ID,1)= Left([Forms]![Your
DataInputForm]![TextBox],1) and increment (plus) one.
If you do not add a phony record for each of the alphabet like A0000, B0000,
etc you will need an IFF statement for when no records match.
 
There's good news and bad news...

First, the bad news ... Access doesn't have a built-in function to do this.

Then the good ... you do NOT have to manually assign the values yourself.

And more bad !!! you WILL have to write a little bit of code that will take
the person's last name, find the first letter, look up how many last names
start with that letter, then create a new number one larger than the current
count.

But rather than creating a field that holds three separate pieces of data
(the first letter of last name, the "-", and a sequence number), you only
need a field for the sequence number. You can always concatenate the first
letter of last name plus "-" plus sequence number if you need to show it in
a query or form or report.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Oops! I just figured out how to break that code!

If someone sneaked in and deleted Don Baker, the count would be one less, so
your "next" sequence number would be the same as the last. You better use
the DMax() function to find out the value of the largest sequence number
when the first letter of the last name = ....

OOPS! I just broke it again!

If an auditor were to look at the sequence of, say, "B"s, and saw one number
missing, how would you explain that your "B"s were not entirely sequential?

Let me step back and ask, "Why do you want this numbering?"

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I'd like to get my db to auto generate a unique user ID based on first letter
of last name and the number (say, Don Baker is first B, so he gets B-0001).
Is there a way to do this without manually assigning myself? Thanks.
Click to expand...

maybe if you had a saved query such as:

SELECT Left([lastname],1) AS [Initial Letter], Count(*) AS
[Count of Names]
FROM YourTableName
GROUP BY Left([lastname],1);

then:

?DLookup("[Count of Names]","Query1","[Initial Letter] = """ &
left(Me.LastName,1) & """") + 1
 
I'd like to get my db to auto generate a unique user ID based on first letter
of last name and the number (say, Don Baker is first B, so he gets B-0001).
Is there a way to do this without manually assigning myself? Thanks.
Click to expand...
referring to my last post:

?leftMe.LastName,1) & "-" & cstr(DLookup("[Count of
Names]","Query1","[Initial Letter] = """ & left(Me.LastName,1)
& """") + 1)
 
Michael

(see my second response...)

A Count(*) won't help if a user ever deletes a record in the middle of the
sequence.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Michael

(see my second response...)

A Count(*) won't help if a user ever deletes a record in the middle of the
sequence.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Jeff, yes, you are correct. I guess it is very difficult to
generate your own incrementing key and never have it break.
 
I have faced a similar situation, and have come up with something like the
following. As a caveat, this involves storing the first letter of the last
name in the ID_Number field, which violates normalization rules. However, I
have struggled and searched and asked questions, and in general have spent
hours and hours trying to avoid including the first letter of the last name
in the ID_Number field, but I cannot find a solution. While I'm at it I
store the hyphen as well, although I could get around that easily enough by
doing some concatenation.
You could use something like this in a command button's Click event, or the
After Update event of the text box for the person's last name. In my test
the table is tblConcat, and the text field in which the number is stored is
ConcatName. Note that the record is saved before the code to assign the
number is run. In a multi-user environment this will help to guard against
the code assigning duplicate numbers if two different users are entering new
records at the same time for people with the same last name.

Me.Dirty = False ' Saves the record
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ConcatName Like """ & Left(Me.LastName, 1) & "*"""
varResult = DMax("ConcatName", "tblConcat", strWhere)

If IsNull(varResult) Then
Me.ConcatName = Left(Me.LastName, 1) & "-001"
Else
Me.ConcatName = Left(varResult, 2) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
Me.Refresh
End If

The line:
If Me.NewRecord Then

could be something like:
If IsNull(Me.ConcatName) Then

or in some other way run the code on demand.

There are some potential problems with your numbering approach, I think.
What happens if somebody's name changes? Is the number reassigned? If the
only person with a last name beginning with "Q" has Q-001, then that
person's name changes, if the original "Q" person gets a new number, the
next person to come along with a last name beginning with "Q" will get the
number Q-001. Now two different people at different times will have the
same number. It may be best to have an Inactive field, but in any case not
to delete name or reuse numbers.
To others who are reading this thread, I would be glad to know a way to
avoid storing the first letter of the last name.
 
I have faced a similar situation, and have come up with something like the
following. As a caveat, this involves storing the first letter of the last
name in the ID_Number field, which violates normalization rules. However, I
have struggled and searched and asked questions, and in general have spent
hours and hours trying to avoid including the first letter of the last name
in the ID_Number field, but I cannot find a solution.

To others who are reading this thread, I would be glad to know a way to
avoid storing the first letter of the last name.
Click to expand...

I suggest you don't fret over it.

There are (at least) two ways of looking at this:

1) Such a sequence number is not a relational key (order of insertion
into the DBMS not being an attribute of the entity) so 'normalization
rules' won't apply.
2) If the letter is part of the key then it cannot be split from the
key, thus the stated aim 'avoid including the first letter'
demonstrates faulty logic. "When we describe First Normal Form, we say
that a data element should hold atomic or scalar values. What we mean
is that if one tries to pull out 'sub-atomic parts' from the value in
a column, the value loses meaning." (Celko, Molecular, Atomic and Sub-
atomic Data Elements - Part 1, http://www.dbazine.com/ofinterest/oi-articles/celko29).
If the 'R' was removed from 'R001' it would surely lose meaning so
don't try to commit the flaw.

Jamie.

--
 
Actually, for my own purposes I just go ahead and do it as I described, but
I think there is something important to learn by figuring out how to do it
without storing the letter. I'm not sure where I would use whatever it is I
could learn by doing it that way, but every problem solved gives me a
potential tool for another project.
One reason for my caveat is that I did not wish to give the appearance of
challenging those who take a different view about redundancy, including some
earlier in the thread. At the same time, my last sentence was asking
somebody who would take a different approach than I just how they would
implement that approach.
I took a look at the article (and bookmarked it for my online library). I
find it somewhat curious that the reasoning for keeping a phone number all
in one column is that the whole number is atomic (the parts are useless by
themselves), but at the same time the elements of a name are atomic, while
the full name is molecular. I think that in most cases it does indeed make
sense to put the entire phone number into a single field, but the reasoning
for doing so is hard to follow. I think the lesson is that the designer
needs to make choices appropriate to the project. The public works
department may need a street-by-street listing in order to contact a person
according to residence, but for most people an alphabetical listing would be
best. In the former case the number and the street may be stored separately
(or maybe the number can be parsed; I'm not looking to solve this particular
issue, but rather to make a point). In the latter case the street and
number would appear together.
 
I
find it somewhat curious that the reasoning for keeping a phone number all
in one column is that the whole number is atomic (the parts are useless by
themselves), but at the same time the elements of a name are atomic, while
the full name is molecular.

I think the lesson is that the designer
needs to make choices appropriate to the project.
Click to expand...

Agreed, therefore a person's full name can be considered atomic where
appropriate.

Jamie.

--
 
As others have mentioned, last names change and you can't really change your
primary key should that happen. That causes your meaningful first letter to
fail to have meaning in some cases. I understand the desire to include a
letter to keep the size of the rest of the number small but I don't think it
should have any meaning outside of its participation in the PK. I would
suggest randomly choosing a letter for each new account. That would allow
you to eliminate I, L, and O since they can be confusing. That achieves
your goal without embedding meaning in a field.
 
I was one of those others who mentioned it. My "similar" situation involved
department abbreviations. They were drawn from a list rather than extracted
from the department name, but the general approach to concatenation was the
same as the example I posted. I suspect that using the last name in a
manner such as the OP described is unlikely to have the intended result over
time.
 
Once a record has been saved, it will never respond true to the "If
Me.NewRecord" query.
 
Yes you did mention it but department has the same drawback as a person's
name. People change departments. The point of my post was to suggest that
when creating a primary key of this nature, it is best to use a randomly
assigned value. In other cases, it might be appropriate to use a fixed set
of non-changeable values such as I for Invoice, R for Refund, D for Order,
etc. so that each type of user visible primary key will start with a
different letter.
 
It seems I was not clear. Certain types of reports in my organization apply
to departments. For the Maintenance department, the reports are numbered
M-07-01, M-07-02, etc. In other words, the letter codes are non-changeable
values such as you have described.
 
No argument from me on that one. Presumably once a number has been assigned
it is permanent. If not, the code without the NewRecord line (and the
corresponding End If) can be run on demand. I suggested a way to check for
a null value in the field rather than a new record, but there are other
possibilities, such as verifying that Left(Me.LastName,1) =
Left(Me.ConcatNumber,1), and many others. Rather than try to cover every
contingency I suggested an option, then waited to see what the OP had to
say, but the OP seems to have dropped out of the discussion.
 
Back
Top