User Account Creation Incrementing Duplicate names

J

Jim_R

I have a SQL database which contains records of all students in my district.
The fields include, school, firstname, lastname, etc. and I want to create
user accounts based on the first name. I would like to run a query that would
take the first name and transform it into a user account name by adding a 1
to it. If there were duplicate first names, it would add a 1 then a 2, so
that each name would be unique:

Jim1
Jim2
Jim3
Jim4
Sally1
Bill1
Dave1
Dave2

How woulld you construct such a query? I see a loop inside a loop.

Jim_R
 
G

Gary Walter

Jim_R said:
I have a SQL database which contains records of all students in my
district.
The fields include, school, firstname, lastname, etc. and I want to create
user accounts based on the first name. I would like to run a query that
would
take the first name and transform it into a user account name by adding a
1
to it. If there were duplicate first names, it would add a 1 then a 2,
so
that each name would be unique:

Jim1
Jim2
Jim3
Jim4
Sally1
Bill1
Dave1
Dave2

How woulld you construct such a query? I see a loop inside a loop.
Click to expand...
Hi Jim,

Does your table have a primary key field
that makes each record distinct (say an
Autonumber field called "ID")?

If so, then you can easily get the "number"
through a ranking query:

SELECT
ID,
FirstName,
(Select Count(*)
FROM yourtable As t2
WHERE
t2.FirstName = t1.FirstName
AND
t2.ID > t1.ID) + 1 As Rank
FROM
yourtable As t1;

{of course, replace "yourtable"
with actual name of your table}

save this query as "qryRank"

Then create an update query
joining original table to this
query on "ID" and update the
"user account name" field in your
original table to

qryRank.FirstName & qryRank.Rank

That might be one way....

good luck,

gary
 
G

Gary Walter

Hi Jim,

Looking back, I may not have given
the "best advice......"

I'm just not sure you want to do this
for dynamic data.

For instance, when you add a new record,
you'll have to update this field possibly using
this same ranking technique.

But, if "Jim3" somehow gets deleted, the next "Jim"
would get "Jim4" which already exists!

It all seems pretty messy to me.

A simple method might be to assume
your "User Account" is

[FirstName] & [ID]

if you actually need a "User Account"

/////////////////////////

Or, you say "SQL database", does that mean
"SQL Server database."

If so, I might just generate a distinct
UserAccountNum (abandoning "name&num")
for each student
through a stored procedure and a 1-row
table in the SQL Server db, something *like*:

CREATE PROCEDURE dbo.GetNewUserAccountNum

/*

Gets last UserAccountNum from
1-row table NewUserAccountNum, increments by 1,
saves back to table and returns result.

*/
AS
SET NOCOUNT ON
DECLARE @NewNum int

SELECT @NewNum = UserAccountNum + 1
FROM NewUserAccountNum WHERE ID = 1
UPDATE NewUserAccountNum
SET UserAccountNum = @NewNum
WHERE ID = 1

RETURN @NewNum


GO


You know best....

good luck,

gary
 
Top