Finding Duplicates for Part of String

[Guest]

Hi,
I have a large testing database consisting of serial numbers of equipment.
From time to time, employees accidentally enter a serial number in twice,
however they may enter it wrong. ie: instead of entering ID12345 they enter
12345. My find duplicates Query however will not pick this up as the fields
are not completely identical. Is there any way for a duplicates query to find
data based on a string being equal to part of a string. partial data below:

Serial Numer Result
ID12345 PASS
ID00001 PASS
ID00002 FAIL
12345 PASS
02 FAIL

Ideally here, I would like a query to identify that serial numbers 12345 and
02 'might' be duplicates of ID12345 and ID00002. any help with this will be
appreciated as this is a major issue for us,

Thanks

Neil

 

[arthurjr07]

SELECT * FROM TABLE1
WHERE TABLE1.[Serial Numer] NOT IN
(SELECT DISTINCT B.[Serial Numer] FROM TABLE1 AS A
INNER JOIN (SELECT * FROM TABLE1) AS B
ON (B.[Serial Numer] = Right(A.[Serial Numer],Len(B.[Serial Numer]))
AND A.[Serial Numer] <> B.[Serial Numer] ))

 

[Guest]

Thanks for you help Arthur,
is there any way to also show the record whose string is contained within
another serial number, ie:

serial number
ID1234
1234

the query will show a result of ID1234. can it also show the 1234, or even
just 1234 on its own without ID1234. the user can do a search then using 1234
and the other record will show

Thanks

Neil

 

[arthurjr07]

You can use this approached,
assuming that
[Forms]![FormName]![txtBoxName] is the parameter
and the [serial number ]
is text field with maximum char of 6 letters.

SELECT * FROM TABLE1 WHERE
TABLE1.[serial number ] = [Forms]![FormName]![txtBoxName]
UNION
SELECT * FROM TABLE1 WHERE
TABLE1.[serial number ] =
RIGHT([Forms]![FormName]![txtBoxName],5)
UNION
SELECT * FROM TABLE1 WHERE
TABLE1.[serial number ] =
RIGHT([Forms]![FormName]![txtBoxName],4)
UNION
SELECT * FROM TABLE1 WHERE
TABLE1.[serial number ] =
RIGHT([Forms]![FormName]![txtBoxName],3)
UNION
SELECT * FROM TABLE1 WHERE
TABLE1.[serial number ] =
RIGHT([Forms]![FormName]![txtBoxName],2)
UNION
SELECT * FROM TABLE1 WHERE
TABLE1.[serial number ] =
RIGHT([Forms]![FormName]![txtBoxName],1)


Hope this will help.

 

[Guest]

Thanks for that, works good now, appreciate your help

neil