Efficient Jet SQL for removing duplicate records?

[AeolusUmbra]

What is the most efficient way to remove duplicate records from a table using Jet SQL? I want to delete redundant records, keeping only one copy of each. Currently I SELECT using a GROUP BY and HAVING COUNT(*)>1, and then I interate through in DAO, deleting redundancies. Is there some better way, perhaps using only a Jet SQL statement? Thanks in advance for your suggestions.

 

[lespaul]

the best way is to not allow duplicate records to be inserted in the first place. Put an index on the table restricting the fields that determine "duplication" and the database won't allow them to be entered.




Leslie

Have you met Hardy Heron?

 

[AeolusUmbra]

> Put an index on the table

Thanks, but that would be manipulating shared tables, which is definately a no-no. I'm looking for a answer that only involves manipulating the data.

 

[MichaelRed]

Deleting records <> manipulating date?

please explain



MichaelRed

 

[AeolusUmbra]

> Deleting records <> manipulating date? please explain

Well this is fundamental:

I posted a question asking for a Jet SQL statement that will remove duplicate records from a database table. Another poster suggested changing the table itself instead, which is clearly a move in the wrong direction. In database work it's important to understand the difference between the data and the thing that holds and manages the data. You don't go punching holes in a bucket just to let out a little extra water.

 

[dhookom]

By "duplicate records" do you mean all the fields are the same? This would suggest there is no primary key. If you mean duplicate set of fields then you some how have to specify which of the duplicate records you want to delete.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

faq701-5721

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

Well, adding an index isn't really making a change to the table. Adding indexes is used by database administrators to increase query/database performance and allow database level rules that prevent bad data from being entered into the table in the first place.
Since your OP makes is sound like you have to do this on a regular basis I thought that suggesting a time honored database way of enforcing your business rules would be preferable to you having to delete records from your table on a regular basis.
My apologies for trying to show you a better way. I'm sure I won't be trying to help you in the future.
Good luck.
Leslie

 

[AeolusUmbra]

> My apologies for trying to show you a better way.

You make all sorts of amateurish assumptions, such as that the duplicate records in the table were unwanted to begin with, instead of being accumulated for counting purposes.

And you assume that I have control over the DB tables, instead of sharing them with others, as is more commonplace in database programming. Changing a table's properties often means having change the code of the many programs that use that table. Clearly you are inexperienced in database programming.

> I'm sure I won't be trying to help you in the future.

If you don't know the answer to a question then it is best not to try substituting the answer to some other question that you happen to think you do know.

 

[dhookom]

AeolusUmbra,
How about replying to (acknowledging) suggestions by others rather than upsetting many of us. So far, three other TT'ers have either asked for clarification or made a suggestion.


Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

I think (s)he is either so ")*)&*(%$&*@#$^(*" full of IT!!!![/color that (s)he cannot deign to respond to us mere mortals OR (s)he does not use english often and is not following the intent of the additional posts.



MichaelRed

 

[BNPMike]

AeolusUmbra

You couldn't be more off target accusing Lespaul of being amateurish.

The question you ask is superficial. A relational database with duplicate records is faulty and will continue to cause problems like the one you see. I agree that's not the question you asked. Did the FAQ not give you a short-term fix?