Delete Duplicates choose survivor

[rockell333]

I want to delete the duplicates in a table based an a primary key however i want to choose the survivor based on another field

[June7]

Okay, provide sample data. Then the selection of survivor is not based on PK, it is based on another field. What is the rule for selection of survivor?

[rockell333]

the survivor would be based on a yes/no field. I want to keep those that are yes. if there is a duplicate one will definiately be yes. if there is no duplicate the field could be either..

primary key name head of household

short version of fields above.. these are households.. each household has an account number. there can be up to two people in one household (husband and wife) one will be the head of household each contact within the household ALSO has an id, in fact the head of household will have the same id as the account number. if there is no duplicate it does not matter whether or not they are a head of household.

so i want to dedupe by household (doing a mailing) but keep the one that is the head of household without loosing the ones without duplicates that are not a head of household

make sense?

[June7]

If the field has different value then the record is not a duplicate.

Why delete? Why not just apply filter?

[rockell333]

for this purpose it is a duplicate. i only want to dedupe on the account number which both records have. i just want to keep the one where the head of household is true. this is for a one time mailer that will be sent to a printer so i do not want to keep the second record in the table

account # name head of household
12345 george true
12345 marian false

in this instance i would want to keep george and not marian

[June7]

So the Yes/No field is to designate the head of household for each account and if there is only one account record the Yes/No field could be either yes or no. Why not set it yes even if only one record?

To find the duplicates, will have to first do a count of the account numbers and determine which has more than 1. Something like:

DELETE FROM table WHERE [YesNoField]=False AND Account IN (SELECT Account FROM table GROUP BY Account HAVING Count(*) >1);

[NTC]

I suggest:
make a stand alone query that you are sure results in the records to be deleted. You just need the key field but other fields for visual check are fine. Call that query TheDeletes

then make a Delete query of the table - dragging in the * symbol for all fields. Add your TheDeletes query in the upper portion of the query design view and join on the key field to the table.

that should work.

one note though - whenever TheDeletes is an aggregate type query, which is not the case for you - then you will get a Not Updateable block - in which case one would write those key values to a temp table. and make a delete query using a join to it instead.....

[rockell333]

just to clarify because that first line of your response is confusing.

Every record has both a contact number (completely unique in the table and currently the primary key) and an account number. The account number simply links the two contacts together as husband and wife.
Since there were multiple parameters in this data pull. not every account will have its head of household in the table and not every account has duplicates.

so are you saying first add a field that counts the how many times the account is in the table and then use the statement above to DELETE FROM mytable WHERE [headofhousehold]=False AND AccountNumber IN (SELECT AccountNumber FROM mytable GROUP BY AccountNumber HAVING countfield(*) >1); ??? this would remove only those records who are not head of household and there is more than one record in the account?

but i do have to create the count field first correct?

[June7]

Whose response in confusing - June7 or NTC?

The count field is not a field in table - it is a calculated field in query.

[rockell333]

sorry NTC hadnt answered yet when i was typing.. so i was reading yours.. and still answering yours

ok.. on the count.. get it.. but i am correct as to what it is doing.. it will only remove the false on those with a count greater than 1?

[rockell333]

seems to have worked perfectly