Macro limited to run only 20 times. How to solve this.

[Mile-O]

Just use the query wizard to do a Find Duplicates Query and once its made, convert it to a Delete Query.

 

[Mike375]

Just use the query wizard to do a Find Duplicates Query and once its made, convert it to a Delete Query.

Mile,

But that would knock out all of the records as opposed to leaving one of the records in each duplicate set which he may want to keep.

Mike

 

[Rich]

Mile,

But that would knock out all of the records as opposed to leaving one of the records in each duplicate set which he may want to keep.

Mike

why don't you try some of the correct solutions instead of keep challenging them

 

[Mike375]

why don't you try some of the correct solutions instead of keep challenging them

Well perhaps Access 95 is different. But if I make a Find Dupilcates query using the Access Wizard it will display ALL of the records in a duplicate set so there for changing the query to a delete quety would knock then all out. Is that correct?

However, if I append to a table with a field that won't accept duplicates then I retain one record from each set of duplicates. If I don't wish to retain one of the records then obviously the delete query is the way to go. But if I (and perhaps this poster) want to keep one of the records from each duplicate set then append to a table that will accept one record from each set of duplicates is the way to go, unless there is some easier way.

I fail see where there is a some challenge to "correct" solutions.

Mike

 

[Rich]

I've no idea what 95 does, it's been so long since I or anybody else has used it. However it was riddled with bugs, maybe that's one of them, but I doubt it.
And why would anybody want to keep duplicates?

 

[Mike375]

I've no idea what 95 does, it's been so long since I or anybody else has used it. However it was riddled with bugs, maybe that's one of them, but I doubt it.
And why would anybody want to keep duplicates?

Firstly, do the later versions of Access list all records that are duplicates...I imagine they would.

And why would anybody want to keep duplicates?

Note that I said....keeping one of the records from the duplicate set. What problem do you see with that. If don't see a problem then my post was not a challenge to a "correct solution"

Mike

 

[Rich]

I'm not sure if the original poster is using one or two tables, but since you're only using one, type, "Automatically delete duplicate records from a table" or just even "duplicates " into the help file search assistant. You'll be suprised at just what you can learn for yourself

 

[Mike375]

I'm not sure if the original poster is using one or two tables, but since you're only using one, type, "Automatically delete duplicate records from a table" or just even "duplicates " into the help file search assistant. You'll be suprised at just what you can learn for yourself

Well show me.

There are four situations where I regularly append to a copy of the table to knock out duplicates, except of course one of then.

Firstly are names lists we buy. With a mixture of string manipulation functions I make all phone numbers the same, that is, remove spaces and brackets etc. and then set the phone number field for no duplicates. I then do a second run on a field that joins first and last name. That is to serve two purposes. One for the obvious reason of not ring the same person several times in a short period. The other is that we run a debit credit type system with companies we buy names from. So we buy 10000 names and it drops to 9700 with duplicates gone plus there might be another 200 with meaningless phone numbers. I pull those out using the Len function.

Secondly are client details that come from some insurance companies who treat a policy number as the "client" and hence Bill Smith might be there from 2 to 10 times. Policy Benefit details of course are in another table.

Thirdly is the table that holds all tekemarketing results. For example, Jim Smith might have 6 records from there was no answer, he said to ring back in a week etc. I append those across to a table so that I am left with one record per person. That table is help separate and serves various purposes not the least being for gov't auditing due to hawking laws in the Financial Services area. I link that table to a copy of the table that has all the records for each call made and run it as a form and subform.

Fourthly, I append the table that holds all appointments to a table that knocks out all but one duplicate so we have a list of all people where a face to face to interview was done. Among other things we alos use this data for Gov't compliance requirements.

There are some others but they are just one offs.

Mike

 

[Rich]

more crap to be moved

 

[Mike375]

more crap to be moved

Suggestions. As you know from the Report if I can do it and gain I will do it. This sort of stuff is easy to change as it is not stuck in the middle of the data base with stuff hanging off it from every direction.

Mike