Email Creation: Detect Duplicate & Update

[Andrew Chan]

Hello all,

Since I am new to code in SQL in MS Access 2003, I would appreciate any
insight from any of you!

I am now creating Email user names from people's first initial of their
first name, and then their last name as the user name, up to 8 characters,
plus the domain.

For example, Robert Washington at school.edu will receive an Email -

(e-mail address removed)

The duplicate problem happens when there is someone with the name Ray
Washington, because Ray will get a duplicate Email just looks like Robert's
-

(e-mail address removed)

Can someone please give me some help after I have populated a column of
Email addresses (with duplicates)?

Here's what I have got:

SELECT Lcase(Mid(Employees.FirstName,1,1)) &
Lcase(Mid(Employees.LastName,1,7)) & "@school.edu" AS Email FROM Employees;

I want to replace the first duplicate Email (Ray's Email) with a numerical
number in increments: 1, 2, 3, 4... etc. So Ray Washington's Email will be -

(e-mail address removed)


Thanks to any help (SELECT DISTINCT? IIF?) in advance!

Andrew