help with duplicates query

[tommyboy]

Could someone please help me with the following?

I have a horse pedigree database where some data has been duplicated. I can currently find and list the duplicate rows matching one criteria with the below (I want the individual rows listed rather than just a 'number of duplicates' column as per the access query wizard).

SELECT [Horse Sub Table].[Horse Name], [Horse Sub Table].Sire
FROM [Horse Sub Table]
WHERE ((([Horse Sub Table].[Horse Name]) In (SELECT [Horse Name] FROM [Horse Sub Table] As Tmp GROUP BY [Horse Name] HAVING Count(*)>1 )))
ORDER BY [Horse Sub Table].[Horse Name];

But to speed things up I only want to show results when two criteria match (both sire and horse name, currently it is only horse name).

Stumping me when trying to modify current query though!?

Thanks in advance

 

[plog]

Divide and conquer.

First make a query to find all Name/Sire permutations with a count >1:

SELECT [Horse Name], [[Sire]
FROM [Horse Sub Table]
GROUP BY [Horse Name], [Sire]
HAVING COUNT([Horse Name])>1

Save that as 'sub1'. Those are all the offenders. Then build another query using sub1 and [Horse Sub Table]. Link them by their matching fields, bring in every field from [Horse Sub Table] and you have all the rows with duplicates

 

[mike60smart]

Hi

Try a UNION Query

Create a query to filter by Horse Name
Create another query to Filter by Sire

Then create a new blank query and do not select any tables to filter , switch to SQL View

Now Open your initial query to filter by Horse Name and switch to SQL View. Copy this SQL and then paste into the new Blank query. Delete the ; at the end of the pasted SQL

Hit the Enter Key and Type UNION

Now open the 2nd query that filters by Sire and switch to SQL View. Copy this SQL and then paste into the new Blank query after the word UNION.

Now if you run this query you should get the desired result.

 

[mike60smart]

Beat to it and it looks like Plog's solution would be better.

 

[tommyboy]

Divide and conquer.

First make a query to find all Name/Sire permutations with a count >1:

SELECT [Horse Name], [[Sire]
FROM [Horse Sub Table]
GROUP BY [Horse Name], [Sire]
HAVING COUNT([Horse Name])>1

Save that as 'sub1'. Those are all the offenders. Then build another query using sub1 and [Horse Sub Table]. Link them by their matching fields, bring in every field from [Horse Sub Table] and you have all the rows with duplicates

Worked a treat.

Many thanks indeed

 

[tommyboy]

Worked a treat.

Many thanks indeed

Is there then a way to allow records to be deleted from those returned results?

Option is greyed out, I guess because of the join between query and table to produce the desired data?

 

[isladogs]

Aggregate queries are read only so you can't delete records shown directly from that. To delete them all is a bit tortuous. Here's one approach

Call the dupes query qryA

Create another query qryB based on your dupes query making sure you include the PK field. Group that to show the first (or last or max or min) value for each dupe pair. These are the records you will keep.

Next make an unmatched query qryC showing all records in qryA not in QryB.
These are the records you want to delete.
Unfortunately that query will also be read only....

So finally make a query joining your table and qryC and change that to a delete query. Run it and you're done. Easy!!!!

Or just delete them manually if there are only a few.

 

[arnelgp]

Didnt it delete all the record with count>1?
I think you need autonumber field so that at least one horse will stay.

 

[tommyboy]

In the end I came up with the following in a simple select query. Seems to work nicely and allows me to delete...

SELECT [Horse Sub Table].[Horse Name], [Horse Sub Table].suffix, [Horse Sub Table].yob, [Horse Sub Table].age, [Horse Sub Table].[Sire ref]
FROM [Horse Sub Table]
WHERE ((([Horse Sub Table].[Horse Name]) In (SELECT [Horse Name] FROM [Horse Sub Table] As Tmp
GROUP BY [Horse Name], [sire ref]
HAVING COUNT([horse name])>1)));

Thanks for all the advice