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

This post has been edited by Brian63: Mar 8 2012, 07:42 AM

(IMG:style_emoticons/default/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

Hi Bob G

thanks for replying

A2010 x32

The row count for the Dup Wizard reports 16k rows

Many thanks
Kind regards

B

i am not running 2010 yet, so we will have to wait for others to jump in

on the deletetion part...
delete where both BatchNo and Ref are equal.

docmd.runsql "Delete from yourTable Where BatchNo = Ref;"

Thanks Bob G

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

before you do anything, make sure to backup your DB just in case

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;"

This post has been edited by arnelgp: Mar 8 2012, 09:07 AM

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

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.