Complex editing of a data table using VBA

[Hattrick16]

I have an access table that looks like Field1 and Field2 and the Desired Action Column shows how I want to chage the table.

Field1 Field2 Desired Action
4321 Keep
5678 Keep
1234 Delete
1234 xx Keep
1234 yy Keep
5678 Delete
5678 xx Keep
5678 yy Delete
7890 Keep

Where there are duplicates in Field1 I want only the record in Field2 where it is "XX".

I was thinking of approaching it this way.. But I am a beginner and Do not have any Idea how to begin.

Step One : Identify the Field one values that have XX in Field2.

Step Two : Extracting all the records where the Field one values that have "XX" in field2 and hold those records.

Step Three : Delete all the records with Field One Values Identified in Step One.

Step Four: Append the Records being Held in Step Two.

 

[Eupher]

Let me see if I have this straight: You want to find all duplicate values in Field1, and for those records with duplicate values, delete all except those that have XX in Field2?

Ken S.

 

[PHV]

I think you can do it in a pure SQL way:
DELETE FROM yourTable AS A
WHERE Nz(Field2, '') <> 'xx'
AND EXISTS (SELECT * FROM yourTable AS B WHERE B.Field1=A.Field1 AND B.Field2='xx');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

I agree with PHV, here's the SQL from a query I made with the "Find duplicates" wizard, then modified to a delete query and to account for the XX value:

DELETE tblDupValues.txtField1, tblDupValues.RecID, tblDupValues.txtField2
FROM tblDupValues
WHERE (((tblDupValues.txtField1) In (SELECT [txtField1] FROM [tblDupValues] As Tmp GROUP BY [txtField1] HAVING Count(*)>1 )) AND ((tblDupValues.txtField2)<>'XX' Or (tblDupValues.txtField2) Is Null));

Ken S.