query all duplicates, with a catch.

[Guest_imported]

I want to construct a query that shows all duplicate records from a table. But the catch is that I have three fields that need tending to, FirstName, LastName, and FullName. Some data I am appending only has contacts listed in full name format, but I have duplicate records of the same contact from another source where the name is in first name - last name format. I don't know how to write in in SQL but I want to ask: select all records where both the first name and last name fields are equal and also select all records where the first name and last name fields of a record are contained within the fullname field of any other record and show the record that has the matching fullname field as well. If you can figure this out I'll buy you a sixer of your favorite beer.

 

[Maquis]

Well, here is a partial solution for you. The only drawback is that the Full name has to exactly match the first and last name fields, and I don't know how often that will match up for you.

SELECT People_1.FullName, People_1.ID
FROM People, People AS People_1
WHERE (((People_1.FullName)=[People]![First Name] & &quot; &quot; & [People]![Last Name]) AND ((People_1.ID)<>[People]![Id]));


This query will do a match on First Name = First Name and Last Name = Last Name.

SELECT People_1.ID, People_1.[First Name], People_1.[Last Name]
FROM People, People AS People_1
WHERE (((People_1.ID)<>[People]![Id]) AND ((People_1.[First Name])=[people]![First Name]) AND ((People_1.[Last Name])=[people]![Last Name]));


I tried to run them both as 1 query with an or statement, but that really sucked up the memory!!

 

[MichaelRed]

I assume the point of this exercise is to generate a single consolidated recordset with no duplicates AND that there are multiple potential sources. If I am 'on track', I would propose a slightly different approach:

[tab]Design the results / Target table with the fields of interest. Do this with LITTLE regard for the various sources, as you need to USE the recordset in YOUR app.

If a primary requirement is the generation of UNIQUE names, have the Primary Key include the FirstName and LastName (You may want or need additional elements, for instance John Smith of Mulbery Street MAY be different from the John Smith of Roland Ave. - and you may have a reason to include BOTH).

For Each of the potential sources, generate a query which gets the information into fields matching your target recordset / table. For Instance, seperate the [FullName] field into [FirstName] and [LastName] fields in aquery based ONLY on the table with the [FullName] Field. Make SURE the field names in the seperate queries are the SAME as the fieldnames in your target table / recordsource.

Decide (and NOTE) which of the potential sources is the most relliable / valid. Write their 'names' down in the order of reliability ...

Change each of the queries to Append queries with your table as the Targts (destination) table.

Run the append queries in the order noted above. After the first one, Ms. Access will huff and puff and complain and NOT add records where the pimary key woud be a duplicate - but 'she' will append all of the non-duplicates to your new table.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over