Find Duplicate within a Field

  • Thread starter Thread starter LDMueller
  • Start date Start date
L

LDMueller

I have a table and I need to run a query to locate duplicates withing a
specific name field.

I created a "Find Duplicates Query" using the wizard, but it only finds
exact matches. This is the code from the query generated by the wizard.

In (SELECT [One] FROM [Anniversary] As Tmp GROUP BY [One] HAVING Count(*)>1 )

I need the query to locate something like the following from the name field.
If I search the name field for "Miller" I need it to find any of the
following:

Name
Ms. Sofia Miller
Mr. Miller James
Constance Miller

Can anyone help?

Thanks in advance!

LDMueller
 
Create a regular Select query including whatever fields you need from
the table. In the criteria row for the name field put;

Like "*" & [What Name?] & "*"

When the query is run, the user will be promted to enter a name, and the
query will return the matching records.

BTW - the word Name should not be used as a field name as it is an Access
reserved word, and you really should separate the name into at least
two different fields (FirstName and LastName). You can still concatenate
them into a single field in a query if you need to. For more on reserved words
see;

http://support.microsoft.com/default.aspx/kb/286335
 
Thank you for your response, I really appreciate it. However, I need it to
run without someone having to type in the criteria, kinda like the "Find
Duplicates Query" does.

Beetle said:
Create a regular Select query including whatever fields you need from
the table. In the criteria row for the name field put;

Like "*" & [What Name?] & "*"

When the query is run, the user will be promted to enter a name, and the
query will return the matching records.

BTW - the word Name should not be used as a field name as it is an Access
reserved word, and you really should separate the name into at least
two different fields (FirstName and LastName). You can still concatenate
them into a single field in a query if you need to. For more on reserved words
see;

http://support.microsoft.com/default.aspx/kb/286335

--
_________

Sean Bailey


LDMueller said:
I have a table and I need to run a query to locate duplicates withing a
specific name field.

I created a "Find Duplicates Query" using the wizard, but it only finds
exact matches. This is the code from the query generated by the wizard.

In (SELECT [One] FROM [Anniversary] As Tmp GROUP BY [One] HAVING Count(*)>1 )

I need the query to locate something like the following from the name field.
If I search the name field for "Miller" I need it to find any of the
following:

Name
Ms. Sofia Miller
Mr. Miller James
Constance Miller

Can anyone help?

Thanks in advance!

LDMueller
Click to expand...
Click to expand...
 
You said:
If I search the name field for "Miller" I need it to find any of the
following:

Name
Ms. Sofia Miller
Mr. Miller James
Constance Miller
Click to expand...

How do you expect the query to find the name "Miller" if you don't,
at some point, enter the name? The search criteria has to come from
somewhere.
--
_________

Sean Bailey


LDMueller said:
Thank you for your response, I really appreciate it. However, I need it to
run without someone having to type in the criteria, kinda like the "Find
Duplicates Query" does.

Beetle said:
Create a regular Select query including whatever fields you need from
the table. In the criteria row for the name field put;

Like "*" & [What Name?] & "*"

When the query is run, the user will be promted to enter a name, and the
query will return the matching records.

BTW - the word Name should not be used as a field name as it is an Access
reserved word, and you really should separate the name into at least
two different fields (FirstName and LastName). You can still concatenate
them into a single field in a query if you need to. For more on reserved words
see;

http://support.microsoft.com/default.aspx/kb/286335

--
_________

Sean Bailey


LDMueller said:
I have a table and I need to run a query to locate duplicates withing a
specific name field.

I created a "Find Duplicates Query" using the wizard, but it only finds
exact matches. This is the code from the query generated by the wizard.

In (SELECT [One] FROM [Anniversary] As Tmp GROUP BY [One] HAVING Count(*)>1 )

I need the query to locate something like the following from the name field.
If I search the name field for "Miller" I need it to find any of the
following:

Name
Ms. Sofia Miller
Mr. Miller James
Constance Miller

Can anyone help?

Thanks in advance!

LDMueller
Click to expand...
Click to expand...
Click to expand...
 
Back
Top