Update Query with autonumber

[Guest]

I have an empty field called CONTACT_ID and I want to populate it with
lastname (3chars) / firstname (2 chars) and then a number starting 01 but
only incrementing if the lastname/firstname is not unique.

update so far is :IIf(Len(RTrim([ZZZContact.last
name]))>5,Left([zzzcontact.Last Name],5) & Left([zzzcontact.First Name],2) &
"01",Left([zzzcontact.Last Name],Len(RTrim([ZZZCONTACT.LAST NAME]))) &
Left([zzzcontact.First Name],2) & "01")

hence "01" needs to be changed to the autonumber. Example data currently is
Last Name First Name Contact
LEE ANDY LEEAN01
LEE ANGELA LEEAN01
LEE CHRIS LEECH01

hence I would get duplicates.

 

[OfficeDev18 via AccessMonster.com]

Hello, Paul,

Do the actual processing in VBA after defining the problem in a query. The
query can be defined as follows:

SELECT contact AS Expr1, Count(contact) AS fn
FROM tablename
WHERE (((Count([contact]))>1));

Open this query, which only contains duplicates, as a recordset. If the
recordset has any records, append each unique duplicate with a different
number. Just make sure you start each unique dupe from '01'.

You can embellish my query - you probably need to - to include the last
number that's already used for that unique series of characters, so you won't
mistakenly start your assignments from '01'.

Good luck,

Sam

I have an empty field called CONTACT_ID and I want to populate it with
lastname (3chars) / firstname (2 chars) and then a number starting 01 but
only incrementing if the lastname/firstname is not unique.

update so far is :IIf(Len(RTrim([ZZZContact.last
name]))>5,Left([zzzcontact.Last Name],5) & Left([zzzcontact.First Name],2) &
"01",Left([zzzcontact.Last Name],Len(RTrim([ZZZCONTACT.LAST NAME]))) &
Left([zzzcontact.First Name],2) & "01")

hence "01" needs to be changed to the autonumber. Example data currently is
Last Name First Name Contact
LEE ANDY LEEAN01
LEE ANGELA LEEAN01
LEE CHRIS LEECH01

hence I would get duplicates.

 

[Guest]

Not working. I've created a module which is called from the query but it
brings back an #Error.

Can you show me how in a module I would use this SQL statement?

Hello, Paul,

Do the actual processing in VBA after defining the problem in a query. The
query can be defined as follows:

SELECT contact AS Expr1, Count(contact) AS fn
FROM tablename
WHERE (((Count([contact]))>1));

Open this query, which only contains duplicates, as a recordset. If the
recordset has any records, append each unique duplicate with a different
number. Just make sure you start each unique dupe from '01'.

You can embellish my query - you probably need to - to include the last
number that's already used for that unique series of characters, so you won't
mistakenly start your assignments from '01'.

Good luck,

Sam

I have an empty field called CONTACT_ID and I want to populate it with
lastname (3chars) / firstname (2 chars) and then a number starting 01 but
only incrementing if the lastname/firstname is not unique.

update so far is :IIf(Len(RTrim([ZZZContact.last
name]))>5,Left([zzzcontact.Last Name],5) & Left([zzzcontact.First Name],2) &
"01",Left([zzzcontact.Last Name],Len(RTrim([ZZZCONTACT.LAST NAME]))) &
Left([zzzcontact.First Name],2) & "01")

hence "01" needs to be changed to the autonumber. Example data currently is
Last Name First Name Contact
LEE ANDY LEEAN01
LEE ANGELA LEEAN01
LEE CHRIS LEECH01

hence I would get duplicates.