Look for duplicates, rename field value, reconcatenate

[Guest]

I want to run some type of query that will concatenate values from three
fields: NG2, Corner, and typecode in another field called XPS_name.

I then want the query to check for duplicates in the XPS_name field.
Whenever there are duplicates, the query should then iteratively assign
values a-z the respective Corner fields, and then re-concatenate to derive a
unique XPS_name.

For example:
two records have NG2= ABC001, Corner=1n, and typecode = c. The first step
will concatenate the XPS_name to "ABC0011nc" for both records. The second
step will find these duplicate entries, the next step will re-assign the
corner field of the first entry to "a" and the second entry to "b". The next
step will re-concatenate. It would also be nice to create a table of all of
the renamed entries. I know how to concatenate and find duplicate entries,
but i don't know how to iteratively assign new values to records that end up
in the duplicate table and i don't know how to create a new table from the
records that are getting updated XPS_name values.

As always all help is appreciated. Thank you, Ruben.

 

[Graham Mandeno]

Hi Ruben

You can't do this with a query, as far as I know. You can write a query to
find the duplicates, of course, but you will need to write some code to do
the update.

Something like this (WARNING: UNTESTED AIR CODE!)

Dim db as DAO.Database, rs1 as DAO.Recordset, rs2 as DAO.Recordset
Dim iCount as Integer, sSQL as String
Set db = CurrentDb
sSQL = "Select NG2, Corner, typecode from [Your table] " _
& "group by NG2, Corner, typecode having Count(*)>1;"
Set rs1 = db.OpenRecordset( sSQL, dbOpenForwardOnly )
Do Until rs1.EOF
sSQL = "Select Corner from [Your Table] where NG2='" & rs1.NG2 _
& "' and Corner='" & rs1.Corner & "' and typecode='" & rs1.typecode
& "'"
Set rs2 = db.OpenRecordset( sSQL )
iCount = 0
With rs2
Do Until .EOF
.Edit
.Corner = Chr( Asc("a") + iCount )
.Update
.MoveNext
iCount = iCount + 1
Loop
.Close
End With
rs1.MoveNext
Loop
rs1.Close

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Jose Ruben Gonzalez-Baird"

 

[Guest]

I'll try that out. Thank you

Hi Ruben

You can't do this with a query, as far as I know. You can write a query to
find the duplicates, of course, but you will need to write some code to do
the update.

Something like this (WARNING: UNTESTED AIR CODE!)

Dim db as DAO.Database, rs1 as DAO.Recordset, rs2 as DAO.Recordset
Dim iCount as Integer, sSQL as String
Set db = CurrentDb
sSQL = "Select NG2, Corner, typecode from [Your table] " _
& "group by NG2, Corner, typecode having Count(*)>1;"
Set rs1 = db.OpenRecordset( sSQL, dbOpenForwardOnly )
Do Until rs1.EOF
sSQL = "Select Corner from [Your Table] where NG2='" & rs1.NG2 _
& "' and Corner='" & rs1.Corner & "' and typecode='" & rs1.typecode
& "'"
Set rs2 = db.OpenRecordset( sSQL )
iCount = 0
With rs2
Do Until .EOF
.Edit
.Corner = Chr( Asc("a") + iCount )
.Update
.MoveNext
iCount = iCount + 1
Loop
.Close
End With
rs1.MoveNext
Loop
rs1.Close

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Jose Ruben Gonzalez-Baird"

I want to run some type of query that will concatenate values from three
fields: NG2, Corner, and typecode in another field called XPS_name.

I then want the query to check for duplicates in the XPS_name field.
Whenever there are duplicates, the query should then iteratively assign
values a-z the respective Corner fields, and then re-concatenate to derive
a
unique XPS_name.

For example:
two records have NG2= ABC001, Corner=1n, and typecode = c. The first step
will concatenate the XPS_name to "ABC0011nc" for both records. The second
step will find these duplicate entries, the next step will re-assign the
corner field of the first entry to "a" and the second entry to "b". The
next
step will re-concatenate. It would also be nice to create a table of all
of
the renamed entries. I know how to concatenate and find duplicate entries,
but i don't know how to iteratively assign new values to records that end
up
in the duplicate table and i don't know how to create a new table from the
records that are getting updated XPS_name values.

As always all help is appreciated. Thank you, Ruben.