finding duplicated id in a query

[Hlarkin]

I am trying to build a inbreeding query. The basis of this is that I need to
find dog's in a certain pedigree that have the same ID number

Below is just PART of a pedigree query. Much more is involved.

SELECT tblDOG.ID, tblDOG.name AS dogname, sire.ID AS sireid, sire.name AS
sname, dam.ID AS damid, dam.name AS dname, ssire.ID AS ssireid, ssire.name AS
ssname, sdam.ID AS sdamid, sdam.name AS sdname
FROM (((tblDOG LEFT JOIN tblDOG AS sire ON tblDOG.sireID = sire.ID) LEFT
JOIN tblDOG AS dam ON tblDOG.damID = dam.ID) LEFT JOIN tblDOG AS ssire ON
sire.sireID = ssire.ID) LEFT JOIN tblDOG AS sdam ON sire.damID = sdam.ID
WHERE (((tblDOG.ID)=1));

The dog's id is 1 this is the pedigree of a certain dog. Now I want to find
all the dog's in this pedigree that have matching ID numbers. Basically if
sireid = 10 and ssireid = 10 I want to know.

OR if I could list all the dog's in another table and then find the dog's
that are related from there.

I am not sure how to accomplish this. I have tried to create a new table,
but because of the WHERE (((tblDOG.ID)=1)) I still get the repeated pedigree.

I think I am setting this up wrong.

 

[Klatuu]

Try using the find duplicates query wizard. It should do what you are wanting.

 

[Hlarkin]

Thanks,
That did help with little manipulating the SQL code.

BUT it shows a count of duplicated fields. How do I display the fields or
know which fields are duplicated?

SELECT First(pedigree.ID) AS [ID Field], First(pedigree.NAME) AS [NAME
Field], First(pedigree.sireid) AS [sireid Field], First(pedigree.ssireid) AS
[ssireid Field], First(pedigree.sdamid) AS [sdamid Field],
First(pedigree.sdsireid) AS [sdsireid Field], First(pedigree.sssireid) AS
[sssireid Field], First(pedigree.dsireid) AS [dsireid Field],
First(pedigree.Ddamid) AS [Ddamid Field], Count(pedigree.ID) AS NumberOfDups
FROM pedigree
GROUP BY pedigree.ID, pedigree.NAME, pedigree.damid, pedigree.sireid,
pedigree.ssireid, pedigree.sdamid, pedigree.sdsireid, pedigree.sssireid,
pedigree.dsireid, pedigree.Ddamid;

 

[Klatuu]

You would have to write another query to return the rows for those that have
duplicates to see the actual values. You can use the duplicates query as a
subquery in a WHERE clause to return the records that have the duplicates.
--
Dave Hargis, Microsoft Access MVP

Thanks,
That did help with little manipulating the SQL code.

BUT it shows a count of duplicated fields. How do I display the fields or
know which fields are duplicated?

SELECT First(pedigree.ID) AS [ID Field], First(pedigree.NAME) AS [NAME
Field], First(pedigree.sireid) AS [sireid Field], First(pedigree.ssireid) AS
[ssireid Field], First(pedigree.sdamid) AS [sdamid Field],
First(pedigree.sdsireid) AS [sdsireid Field], First(pedigree.sssireid) AS
[sssireid Field], First(pedigree.dsireid) AS [dsireid Field],
First(pedigree.Ddamid) AS [Ddamid Field], Count(pedigree.ID) AS NumberOfDups
FROM pedigree
GROUP BY pedigree.ID, pedigree.NAME, pedigree.damid, pedigree.sireid,
pedigree.ssireid, pedigree.sdamid, pedigree.sdsireid, pedigree.sssireid,
pedigree.dsireid, pedigree.Ddamid;

Try using the find duplicates query wizard. It should do what you are wanting.