Finding possible duplicates in multiple queries: how to join?

[tyemm]

Given a number of possible ways in which duplicates might exist, I created several different duplicates seach queries: one looked for Count(*) where >1 of Last & First; another looked for the same where Last and SSN; a third for Last and DOB, etc.

Now I'd like to combine all of these and end with a Distinct query, to arrive at the full list of real and possible (because of misspellings, etc.) duplicates. Do I need to create a make table query and then do this?

 

[dhookom]

If I understand correctly, you could use a union query. It's a bit difficult since we don't know what fields/columns you are using in your queries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tyemm]

I have, all from one table [tbl PossibleDups]: Last, First, Middle, SSN, DOB, etc.

I have created several queries, each one looking for duplicates where I am examining some but not all of these fields. Thus I have qry Dup Last First; qry Dup Last SSN; qry Last DOB, etc.

There's plenty of overlap between them, but I'd like to combine all of them and then take a DISTINCT query out of that mix. I have Last (name) in each one, but no other field is in every query.

This sounds like a standard mailing list type of problem, where time and typos have created duplicates that any sane person would wish to remove: how many of us have received duplicate catalogs!

Thanks--

 

[dhookom]

You should have a primary key field in your table that might help identify dups.

I would still recommend a union query. If you can't figure out how to do this, come back with the SQL view of at least two of your queries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tyemm]

Believe me, if there had been Primary Keys in the original generation of this list, there would not have been so many duplicates created, in so many 'creative' ways! Here examples of two of my dup queries; I'd like to use all four of them, but this should suffice:

SELECT [PossDups].[Last Name], [PossDups].[First Name], [PossDups].DOB, Count(*) AS Dups
FROM [PossDups]
GROUP BY [PossDups].[Last Name], [PossDups].[First Name], [PossDups].DOB
HAVING (((Count(*))>1));

the other one is

SELECT [PossDups].[Last Name], [PossDups].DOB, Count(*) AS Dups
FROM [PossDups]
GROUP BY [PossDups].[Last Name], [PossDups].DOB
HAVING (((Count(*))>1));

 

[dhookom]

I would try something like:

SELECT "LastFirstDOB" as Flds, [PossDups].[Last Name] as Fld1, [PossDups].[First Name] as Fld2, [PossDups].DOB as Fld3, Count(*) AS Dups
FROM [PossDups]
GROUP BY [PossDups].[Last Name], [PossDups].[First Name], [PossDups].DOB
HAVING (((Count(*))>1))
UNION ALL
SELECT "LastDOB", [PossDups].[Last Name],Null, [PossDups].DOB, Count(*)
FROM [PossDups]
GROUP BY [PossDups].[Last Name], [PossDups].DOB
HAVING (((Count(*))>1));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]