Amend sql for find duplicates query (1 Viewer)

[guinness]

Hi guys

I have a find duplicates query with the following SQL:

 SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome
FROM tblData
WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1  And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4])))
ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;

This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match

So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.

As always I don't know where I would be without your help.

Cheers

 

[spikepl]

Run the duplicate query wizard

 

[guinness]

Thanks Spike

It was the duplicate query wizard I used to get the above finding duplicates where the three fields matched. The problem is that there may be three duplicates matching that are all version 1 and a further two that are version 2. Total of 5 duplicates. I only want it to find 3 duplicates.

So compare the whole table. Ignore the duplicates which are version 1= Version 1 but show the duplicates where version 2 = version 1.

 

[guinness]

What I ended up doing was creating two queries. One filtered on version 1 and one filtered on version 2. I then created a third query with joins on the three fields I wanted to match, vendor etc.

Messy but it got me there.

Thanks again for taking a look.

Cheers