Full Version: Removing (mostly) Redundant Rows In A Table
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Brian63
Hi all. 1st post on this site.

I have a table which contains data from two source tables.

In some cases I notice I have two rows which have exactly the same population except three fields. In these cases I need to remove one row from the table.

So the field with the key information to use to do this is Batchno

BatchNo Ref

34567 34567
10023 34567

I think I need to write the logic below into a query (still really rubbish at SQL Code)

find both where (appearing on separate rows)

(Row 1) where Batchno = Ref
And
(Row 2) where Batchno not equal to Ref


Then Delete Row Where Batchno IS equal to Ref

Any help would be marvellous

Cheers
B
Bob G
welcome2UA.gif

what version of access are you using?

you can also look at the query wizard for finding duplicates within a table. that will at least tell you how many you have to work with
Brian63
Hi Bob G

thanks for replying

A2010 x32

The row count for the Dup Wizard reports 16k rows

Many thanks
Kind regards

B
Bob G
i am not running 2010 yet, so we will have to wait for others to jump in
arnelgp
on the deletetion part...
delete where both BatchNo and Ref are equal.

docmd.runsql "Delete from yourTable Where BatchNo = Ref;"
Brian63
Thanks Bob G
Brian63
Hi arnelgp

thanks for replying

Do I open a new query
Add Table
Open SQLView

Copy and paste the code (amended showing myactualtablename you provided into the SQL box

Save and run?

Cheers
Rob
Bob G
before you do anything, make sure to backup your DB just in case
arnelgp
As Bob said, make backup first, there is no undelete process when you run the code.
Yes you replace yourTableName with the table with which you will delete records.
Open the the VBA Editor, I dont have my MS 2010 right now, so just press Alt+F11 to invoke it.
If the immediate window is not displayed, goto the View immediate window or Ctrl+G.
Start typing(in immediate window, usually at the bottom right of screen): docmd.RunSQL "Delete from yourTableName Where BatchNo = Ref;"
Brian63
Hi,

Back up made.

I have typed in the (location amended) code into the Immediate Window.

Sorry about this, but I have never used the VBA Editor before.

What do I do now?

As before
Many thanks


B
Brian63
Hi,

I used the logic in the SQL code, provided by arnelgp, to create a query. Tested the query as Select.

I then changed the query type to Delete and ran it.

Job done.

Thank for your valuable input.

B.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.