MS ACCESS LOOP and Delete

[snowmman99]

Is there a way to loop thru looking for duplicated values in a table "column1" and look at the next row in column2 for a value and if the value equals BBB then delete it?

column1 column2

AAA
AAA
-------------BBB

CCC
CCC
CCC
-------------BBB

 

[kjv1611]

Yes. There are ways of doing this.

The most efficient way I can think of is actually just doing a query, either building the SQL code in VBA or just using the Query design tool.

So, I'll go with your example. The SQL code could be something like this:

DELETE  a
FROM    Table1 a
      INNER JOIN
   (
    SELECT column1
    FROM   Table1 b
    GROUP BY b.column1
    WHERE COUNT(1) > 1
   ) c
      ON a.Column1 = b.Column1
WHERE   column2 = "BBB"

Although, I think I'm missing something in the code. I think the DELETE part has to be done a little different.

To get the same effect with the query design, I'd just create a query that returns (so a select query) only those records with duplicates (that'd be the subquery, above).

Then I'd create a new query with the same table, and join to the duplicate query, and then add the condition on column 2 for "BBB". Then make sure the query is set to a delete query.

Of course, if this data is very important, I'd create a back-up of the table before running everything just in case there is any error in the code or logic.

 

[snowmman99]

Thanks for the input. I am trying to get it to work but
the problem is the column one has the duplicate records and some of the duplicates will not be deleted.. all depending on column 2 data which does not have the duplicate value in column 1.

Still trying to move forward..
Thanks

 

[kjv1611]

Hmm, something I just thought of. I don't know if Access has this built in like Excel does now, but if not, here's my thought.

In Excel 2007, on the data tab, there's a "Remove Duplicates" button. When you click it, it'll ask you which column contains the duplicate data. And upon your approval, it'll remove the duplicates, saving 1 of each duplicate pair/group.

It's really a beautiful thing. I've tried it on a few different types of data, and it's worked perfectly every time.

So, my thought there is that (if you're using 2007 or 2010), you can export the data to Excel, so long as it's less than 1 million rows of data, and then run the de-dup button on that... and then send it back to Access.

That might be easier than typing your own code to handle it in Access. Now I'm curious as to what Access might have in that same area... I know you can do Group By with a query and all of that, but the Excel method seems SO much easier.

 

[kjv1611]

Oh, and I didn't think to state this in my first reply, but being this sounds like you are wanting to do this in code, I'd suggest starting a thread here:
forum705

You can still talk code here, but it should be in reference specifically to a user form. This sounds like it doesn't require a user form at all.

 

[snowmman99]

Thanks for the input. I will try the VBA coding area.