Finding partial matched duplicates across two fields

[Sardonic]

Dear All,

I working with a 23,000 record database where the sources for individual
contacts have been taken from different sources. Using the duplicates query
wizard gives me a partial solution.

Each Record has "First Name" and "Last Name". Some records are "John",
"Smith" but there are also duplicates with just the first initial (ie, "J",
"Smith") What I need is a duplicate finder that will sort by the full last
name first, and then by the first character of the first name. So that
"Smith", "J" will be next to "Smith", "J"., rather than all the Smiths in.

What kind of query could do this?

Cheers

Sardonic

 

[Gary Walter]

I working with a 23,000 record database where the sources for individual
contacts have been taken from different sources. Using the duplicates
query
wizard gives me a partial solution.

Each Record has "First Name" and "Last Name". Some records are "John",
"Smith" but there are also duplicates with just the first initial (ie,
"J",
"Smith") What I need is a duplicate finder that will sort by the full
last
name first, and then by the first character of the first name. So that
"Smith", "J" will be next to "Smith", "J"., rather than all the Smiths in.

http://www.fmsinc.com/free/newtips/sql/sqltip8.asp

***quote***

SELECT
Employees.LastName, Employees.FirstName, Employees.Address,
Employees.EmployeeID, Employees.Title

FROM
Employees

WHERE
(
(
(Employees.LastName) In
(
SELECT [LastName]
FROM [Employees] As Tmp
GROUP BY [LastName],[FirstName],[Address]
HAVING
Count(*)>1
And
[FirstName] = [Employees].[FirstName]
And
[Address] = [Employees].[Address]
)
)
)

ORDER BY
Employees.LastName, Employees.FirstName, Employees.Address


This query will return
the first name, last name, address, employeeid and title of all rows
where
the last name, first name, and address are duplicated.
***unquote***

what constitutes "dups" is determined by
GROUP BY in subquery, i.e.,

GROUP BY
[LastName],
[FirstName],
[Address]

it sounds like you want

(
SELECT
t.LastName
FROM
[Employees] As t
GROUP BY
t.LastName,
Left(t.FirstName,1)
HAVING
Count(*)>1
And
Left(t.FirstName],1) = Left([Employees].[FirstName],1)
)

 

[Gary Walter]

Looking back I may have answered
something you didn't ask.

In FMS example, the ORDER BY
clause will sort all "Smith, J" 's together.

It might help if you posted the SQL
of the query that you have so far.

{possibly unneeded instructions}

Go into SQL View,
copy that to clipboard,
then paste back here.

I working with a 23,000 record database where the sources for individual
contacts have been taken from different sources. Using the duplicates
query
wizard gives me a partial solution.

Each Record has "First Name" and "Last Name". Some records are "John",
"Smith" but there are also duplicates with just the first initial (ie,
"J",
"Smith") What I need is a duplicate finder that will sort by the full
last
name first, and then by the first character of the first name. So that
"Smith", "J" will be next to "Smith", "J"., rather than all the Smiths
in.

http://www.fmsinc.com/free/newtips/sql/sqltip8.asp

***quote***

SELECT
Employees.LastName, Employees.FirstName, Employees.Address,
Employees.EmployeeID, Employees.Title

FROM
Employees

WHERE
(
(
(Employees.LastName) In
(
SELECT [LastName]
FROM [Employees] As Tmp
GROUP BY [LastName],[FirstName],[Address]
HAVING
Count(*)>1
And
[FirstName] = [Employees].[FirstName]
And
[Address] = [Employees].[Address]
)
)
)

ORDER BY
Employees.LastName, Employees.FirstName, Employees.Address


This query will return
the first name, last name, address, employeeid and title of all rows
where
the last name, first name, and address are duplicated.
***unquote***

what constitutes "dups" is determined by
GROUP BY in subquery, i.e.,

GROUP BY
[LastName],
[FirstName],
[Address]

it sounds like you want

(
SELECT
t.LastName
FROM
[Employees] As t
GROUP BY
t.LastName,
Left(t.FirstName,1)
HAVING
Count(*)>1
And
Left(t.FirstName],1) = Left([Employees].[FirstName],1)
)