Update foreign key of duplicate records

[wvandenberg]

Hello,

I have modified the structure of my database a little bit and now I am faced with removing duplicate records from a table. The two tables I'm working with are joined 1(tblAnalytes.pkAnalyteID)-to-many(tblResults.fkAnalyteID) as described below:

tblAnalytes          tblResults
  pkAnalyteID          pkResultID
  AnalyteName          fkAnalyteID
                       ResultValue

The I would like to delete all the duplicates from tblAnalytes, leaving just one instance. However, I must first update all those duplicates in tblResults before I can delete any of the duplicate records from tblAnalytes. Here is my "Find duplicates for tblAnalytes" query (created with the wizard):

SELECT tblAnalytes.AnalyteName, tblAnalytes.pkAnalyteID
FROM tblAnalytes
GROUP BY tblAnalytes.AnalyteName, tblAnalytes.pkAnalyteID
HAVING (((tblAnalytes.AnalyteName) In (SELECT [AnalyteName] FROM [tblAnalytes] As Tmp GROUP BY [AnalyteName] HAVING Count(*)>1 )))
ORDER BY tblAnalytes.AnalyteName;

I created another query, joining the "Find duplicates for tblAnalytes" and tblResults

SELECT tblResults.pkResultID, tblResults.fkAnalyteID, tblResults.ResultValue
FROM tblResults INNER JOIN [Find duplicates for tblAnalytes] ON tblResults.fkAnalyteID = [Find duplicates for tblAnalytes].pkAnalyteID;

This is where I am stuck. I would like to determine the smallest primary key of each set of duplicates (or in some instances, triplicates etc.) and update all the related records in tblResults. If anyone can make sense if this, I would really appreciate any suggestions.

Thanks,
Wendy

 

[PHV]

determine the smallest primary key of each set of duplicates

SELECT AnalyteName, Min(pkAnalyteID) AS pkAnalyteIDmin
FROM tblAnalytes
GROUP BY AnalyteName
HAVING Count(*)>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvandenberg]

Thanks, that's just what I needed to get going.