Finding Dupes.

[Tommy B]

Hi Guys,

I have a table full of names and address's that I want to de-dupe. How do I find instances where Name, Addressline, Town, County, Postcode are identical. Not duplicates individually but the as if all were concatinated together.

Any idea's?

Cheers,

Tommy B

 

[Hayley Baxter]

Hi Tommy

If you create a new query and use the find duplicates wizard. You can then choose the field(s) you wish to show in your query results.

Hay

 

[Tommy B]

Hi Hayley,

Thanks for the reply I think my description must have been off as what I am trying to do is only show records where all the fields are the same. So not records where name matches but the rest don't. Only records where Name, Address, Town, County, etc are all identical. The find dupes query gives me all instances where any one field is duplicated but not ones where all match. The point being that two people from the same house could reply but only the name will be different, in which case I wouldn't want them flagged as dupes. Does this make any sense?

Thanks again for the reply anyway

Tommy B

 

[Hayley Baxter]

Hi Tommy

Yes I understood you correctly but thought the find duplicates wizard would have allowed you to do this. I have only used it once so perhaps it only allows one field to be used but I was sure I saw that you could add other fields anyone else?

 

[ColinEssex]

Just butting in here - excuse me Hay - but can't you use a groupBy query with a count and select only the fields you have identical data for, then in the count field criteria put >1 and it'll give you all the doubles and trebles etc.

You can then use that as a basis for a delete query to remove them.

Col

 

[Tommy B]

Hi Hayley,

You are totally right on the wizard front However as I said it appears to look at each field in isolation rather than as a concatinated string which is what I need. I guess i could create a new field that is the concatinated result of the others and query that, but I am interested to see if this can be accomplished without my clumsy solution. Again thanks for helping though mate, the interest is appreciated

Tommy B

 

[Tommy B]

Hi Col,

Butt in all you like I will give your solution a try, I think I see where you are coming from. Thanks for the help both of you

Cheers,

Tommy B