Checking for duplicates

[Stephanie]

I have a table with two fields I want to check for duplicates on (FirstName
and LastName). After I enter first and last name, I want it to check the
table for duplicates. If there is a duplicate, I want to be ableto append
that record.

Can any one help me?

 

[Sergey Poberezovskiy]

Stephanie,

If you need to see all the combinations for LastName, FirstName that appear
multiple times in your table, you could use the following query:
select
LastName, FirstName
from
MyTable
group by
LastName, FirstName
having count(*) > 1
order by 1, 2

If you want to see the actual records in the table that are duplicates, then
you could run the following query

select
t.*
from
MyTable As t
inner join
(select LastName, FirstName from MyTable group by LastName, FirstName
having count(*) > 1) As m
on t.LastName = m.LastName and t.FirstName = m.FirstName
order by
t.LastName, t.FirstName

HTH

 

[Stephanie]

I want to be able to locate the duplicate records when I type the names into
a form. I'll type in the first name, then the last name. Then when the last
name no longer has focus, I want to to get a message.

 

[Sergey Poberezovskiy]

Stephanie,

Do you have a primary key on the underlying table (MyTable) such as
autoNumber field? Knowing the structure of the table would help writing the
code.