deleting dupe rows

[turbogoat]

Hi there,

just build a cracking database and realized that i left a important part out !

Rather than build it again I have a plan on how I can make it go unnoticed )

Could anyone please please please give me a hand with some code ?

What i require is some vba or sql which on the click of a button will go through all the rows in my table and delete rows if column A is a dupe of another column A but column B is blank where as the other row has column A and Column B populated.

Any help would be much appreciated

TY

 

[bpaquette]

isn't there a special kind of query you can run for that? check the help files for somethig about duplicate records or something

bp

 

[FoFa]

It is better to prevent than fix. A little bit of code to check for an existing value may not be all that hard to resolve your issue. However try this:
Assumptions (PK = Primary Key):
Tbl1 - PK, ColA, ColB
DELETE From Tbl1
WHERE PK in (SELECT PK from Tbl1 WHERE ColA in (SELECT ColA from Tbl1 GROUP BY ColA HAVING COUNT([ColA]) > 1) AND ColB IS NULL)

This is not really good in Access, but if you base queries off of an agregate query in Access it usually will not let you update. Remember SQL is a SET language, so basically you need a SET of ColA where there is more than ONE, and then any of those Where ColB is null. Access does not handle SubQueries really good either, So in the long run you would be better off trying to prevent rather than fix. I am not sure the above will work in Access (would in other DB's, but Access gets persnickity sometimes).