Duplicates by Partial Matching (MS ACCESS)

[apatel85]

Hi,

I am trying to find duplicates from one table using one column as the
criteria. However, some of the value in the column differs from one
and another, but yet they are duplicates.

For instance:

Number Name Col C AMT
C3500 John Smith 12/01/05 1200
3500 John Smith 12/01/05 1200

3200PS Joe Smith 12/03/05 1300
3200P Joe Smith 12/03/05 1300

0002 James 12/09/05 1350
02 James 12/09/05 1350

In the above instances, all the records are duplicated but when using
"Number" as criteria to look for duplicates, it will not be consider as
duplicates as they have either letters included or excessive zeros in
the front.

Can you guys please tell me how can I create query in MS Access so that
it will omit all the alphabetic in the "NUMBER" column and omits
the zero in front of any numbers?

Thanks,

-Ankit


Additionlly,

 

[Marshall Barton]

I am trying to find duplicates from one table using one column as the
criteria. However, some of the value in the column differs from one
and another, but yet they are duplicates.

For instance:

Number Name Col C AMT
C3500 John Smith 12/01/05 1200
3500 John Smith 12/01/05 1200

3200PS Joe Smith 12/03/05 1300
3200P Joe Smith 12/03/05 1300

0002 James 12/09/05 1350
02 James 12/09/05 1350

In the above instances, all the records are duplicated but when using
"Number" as criteria to look for duplicates, it will not be consider as
duplicates as they have either letters included or excessive zeros in
the front.

Can you guys please tell me how can I create query in MS Access so that
it will omit all the alphabetic in the "NUMBER" column and omits
the zero in front of any numbers?

For a Jet database, the general approach would be to create
a public function that parses the numeric part of the field
and returns its integer value. Here's an air code example:

Public Function GetNum(S as String) As Integer
Dim k As Integer
For k = 1 to Len(S)
If Mid(S, k, 1) Like "#" Then
GetNum = Val(Mid(S, k))
Exit For
End If
Next k
End Function

Then your query would use GetNum(field) instead of just
field.

 

[apatel85]

Hey Marshall:

Thanks for the reply but I am totally confused now. Where would I able
to execute the code that you have provided? Also, after execution,
would I able to copy the result into new file automatically?

Thanks,

Ankit

 

[bigalst]

Hello,

I have a similar problem. I tried your function in Access2003 and got
an "Overflow" runtime error.

Here is a sample of the data:

BA4053
GGS46132
DDJ05312D

I need to strip out the leading alpha characters into a new field and
the trailing alpha characters into a different field. Any alpha
characters (or symbols) in the middle should be ignored.

For example:
BA889Z98D
Would be split into three fields: BA 889Z98 D

BA4053
Would be split into two fields: BA 4053

Thank you for any help!

 

[John Nurick]

I'd probably do this by using the rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm in an update query.

Used in calculated fields, these two expressions will get you the
beginning and ending substrings of alpha characters:

BegAlpha: rgxExtract([MyField], "^[A-Z]+")
EndAlpha: rgxExtract([MyField], "[A-Z]+$")

 

[bigalst]

Thanks John!

That worked perfectly!

-bigal

I'd probably do this by using the rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm in an update query.

Used in calculated fields, these two expressions will get you the
beginning and ending substrings of alpha characters:

BegAlpha: rgxExtract([MyField], "^[A-Z]+")
EndAlpha: rgxExtract([MyField], "[A-Z]+$")

Hello,

I have a similar problem. I tried your function in Access2003 and got
an "Overflow" runtime error.

Here is a sample of the data:

BA4053
GGS46132
DDJ05312D

I need to strip out the leading alpha characters into a new field and
the trailing alpha characters into a different field. Any alpha
characters (or symbols) in the middle should be ignored.

For example:
BA889Z98D
Would be split into three fields: BA 889Z98 D

BA4053
Would be split into two fields: BA 4053

Thank you for any help!