find duplicate wizard HELP!!!!

Dear all

I have a table with field1:surname and field2:name.
I need to perform a find duplicate query.
My problem is that field2 contains names, writen in a non standard
way, for example:
record1: surname: Smith, name:Andreas
record2: surname: Smith, name:Andr.

Is there a way to compare and find duplicates for field1 and for
field2 but only the first three characters of the name?


I need urgently help.
I would appreciete any suggestion.

Thanks in advance
Stelios

On 4 Oct 2004 08:18:08 -0700, (stelios) wrote:

Dear all

I have a table with field1:surname and field2:name.
I need to perform a find duplicate query.
My problem is that field2 contains names, writen in a non standard
way, for example:
record1: surname: Smith, name:Andreas
record2: surname: Smith, name:Andr.

Is there a way to compare and find duplicates for field1 and for
field2 but only the first three characters of the name?

Sure. You can use the built-in string handling function Left() to
extract the substring.

Create a Query based on the table; select the unique ID field, and in
the next two field cells type

Surname3: Left([Surname], 3)
Name3: Left([Name], 3)

Change the query to a Totals query by clicking the Greek Sigma icon
(like a sideways M); leave the default Group By on Surname3 and Name3,
and change it to Count on the ID field. Put a criterion of

1

on the counted field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Great!
Thanks.


John Vinson wrote in message . ..
On 4 Oct 2004 08:18:08 -0700, (stelios) wrote:

Dear all

I have a table with field1:surname and field2:name.
I need to perform a find duplicate query.
My problem is that field2 contains names, writen in a non standard
way, for example:
record1: surname: Smith, name:Andreas
record2: surname: Smith, name:Andr.

Is there a way to compare and find duplicates for field1 and for
field2 but only the first three characters of the name?

Sure. You can use the built-in string handling function Left() to
extract the substring.

Create a Query based on the table; select the unique ID field, and in
the next two field cells type

Surname3: Left([Surname], 3)
Name3: Left([Name], 3)

Change the query to a Totals query by clicking the Greek Sigma icon
(like a sideways M); leave the default Group By on Surname3 and Name3,
and change it to Count on the ID field. Put a criterion of

1

on the counted field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps