By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Looking for duplicate names

P: n/a
Looking for duplicat names is tough. How many Bob Smiths do you know?
Searching for a Concatenation of:
[lastname]&[firstname]&[address]&[phonenumber] with no spaces between fields
works pretty good but is not fool proof. Are M.I. always used? Is the input
spelling always correct? GIGO is a real problem with databases in general.

Chuck
Jan 15 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Chuck wrote:
Looking for duplicat names is tough. How many Bob Smiths do you know?
Searching for a Concatenation of:
[lastname]&[firstname]&[address]&[phonenumber] with no spaces between fields
works pretty good but is not fool proof. Are M.I. always used? Is the input
spelling always correct? GIGO is a real problem with databases in general.

Chuck
Indeed! If you can devise a way to unduplicate me in credit card
offerers' databases, I shall tip my hat with much veneration...

--
Smartin
Jan 17 '07 #2

P: n/a
"Chuck" <li*****@schoollink.netwrote
Looking for duplicat names is tough. How many Bob
Smiths do you know? Searching for a Concatenation of:
[lastname]&[firstname]&[address]&[phonenumber] with
no spaces between fields works pretty good but is not
fool proof. Are M.I. always used? Is the input
spelling always correct? GIGO is a real problem with
databases in general.
I agree with you and with Smartin. Did you have a question?

Larry
Jan 18 '07 #3

P: n/a
On Thu, 18 Jan 2007 03:48:27 GMT, "Larry Linson" <bo*****@localhost.notwrote:
>"Chuck" <li*****@schoollink.netwrote
Looking for duplicat names is tough. How many Bob
Smiths do you know? Searching for a Concatenation of:
[lastname]&[firstname]&[address]&[phonenumber] with
no spaces between fields works pretty good but is not
fool proof. Are M.I. always used? Is the input
spelling always correct? GIGO is a real problem with
databases in general.

I agree with you and with Smartin. Did you have a question?

Larry
Jan 18 '07 #4

P: n/a
On Thu, 18 Jan 2007 03:48:27 GMT, "Larry Linson" <bo*****@localhost.notwrote:
>"Chuck" <li*****@schoollink.netwrote
Looking for duplicat names is tough. How many Bob
Smiths do you know? Searching for a Concatenation of:
[lastname]&[firstname]&[address]&[phonenumber] with
no spaces between fields works pretty good but is not
fool proof. Are M.I. always used? Is the input
spelling always correct? GIGO is a real problem with
databases in general.

I agree with you and with Smartin. Did you have a question?

Larry
No, thank you, it was just a comment. Someone had asked how to look for
duplicate names but I had failed to save the original post. I was hoping
against hope that the original poster was reading this news group on a regular
basses and would see my post. I think now that my post was a bad idea, or at
least the subject description was badly stated.

Chuck
Jan 18 '07 #5

P: n/a
Chuck wrote:
it was just a comment.
Your comment gave me an idea. Maybe the following can avoid
concatenation issues:

qryDistinctList:
SELECT lastname, firstname, address, phonenumber FROM MyTable UNION
SELECT lastname, firstname, address, phonenumber FROM MyTable;

There's likely to be a simple way to remove duplicates using an
extension of this idea.

tblDuplicates
DID Autonumber
Field1 Text
Field2 Text
Field3 Text

DID Field1 Field2 Field3
1 A B D
2 A C D
3 A B D
4 B C E
5 A C E

qryDistinctList:
SELECT Field1, Field2, Field3 FROM tblDuplicates UNION SELECT Field1,
Field2, Field3 FROM tblDuplicates;

qryUnique:
SELECT First(DID) AS UID, tblDuplicates.Field1, tblDuplicates.Field2,
tblDuplicates.Field3 FROM tblDuplicates INNER JOIN qryDistinctList ON
(tblDuplicates.Field3 = qryDistinctList.Field3) AND
(tblDuplicates.Field2 = qryDistinctList.Field2) AND
(tblDuplicates.Field1 = qryDistinctList.Field1) GROUP BY
tblDuplicates.Field1, tblDuplicates.Field2, tblDuplicates.Field3 ORDER
BY First(DID);

!qryUnique:
UID Field1 Field2 Field3
1 A B D
2 A C D
4 B C E
5 A C E

What if only the combinations of Field1 and Field2 must be unique yet
Field3 must be shown?

qryDistinctList:
SELECT Field1, Field2 FROM tblDuplicates UNION SELECT Field1, Field2
FROM tblDuplicates;

!qryUnique:
SELECT First(DID) AS UID, tblDuplicates.Field1, tblDuplicates.Field2,
First(tblDuplicates.Field3) As FirstField3 FROM tblDuplicates INNER
JOIN qryDistinctList ON (tblDuplicates.Field2 = qryDistinctList.Field2)
AND (tblDuplicates.Field1 = qryDistinctList.Field1) GROUP BY
tblDuplicates.Field1, tblDuplicates.Field2 ORDER BY First(DID);

!qryUnique:
UID Field1 Field2 FirstOfField3
1 A B D
2 A C D
4 B C E

Since there are potentially many Field3 values for the unique
combinations of Field1 and Field2, a choice had to be made about which
one is to be returned. You can get fancy from here about which ones to
keep using subqueries (use the same criteria for both DID and Field3
noting that ordering by DID is not necessary in qryUnique). The most
recent dates or maximum values (price? :-)) are typical choices. An
unmatched query on DID and UID can identify which records to
delete/exclude. I hope these comments give you some ideas.

James A. Fortune
CD********@FortuneJames.com

Jan 18 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.