Mar 8 2012, 07:40 AM
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
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
(Row 2) where Batchno not equal to Ref
Then Delete Row Where Batchno IS equal to Ref
Any help would be marvellous
Mar 8 2012, 07:47 AM
That 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
Mar 8 2012, 07:54 AM
Hi Bob G
Thanks for replying
The row count for the Dup Wizard reports 16k rows
Mar 8 2012, 08:04 AM
i am not running 2010 yet, so we will have to wait for others to jump in
Mar 8 2012, 08:07 AM
on the deletetion part...
delete where both BatchNo and Ref are equal.
ocmd.runsql "Delete from yourTable Where BatchNo = Ref;"
Mar 8 2012, 08:14 AM
Thanks Bob G
Mar 8 2012, 08:18 AM
Thanks for replying
Do I open a new query
Copy and paste the code (amended showing myactualtablename you provided into the SQL box
Save and run?
Mar 8 2012, 08:45 AM
before you do anything, make sure to backup your DB just in case
Mar 8 2012, 09:05 AM
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;"
Mar 8 2012, 09:32 AM
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?
Mar 8 2012, 11:09 AM
I used the logic in the SQL code, provided by arnelgp, to create a query. Tested the query as Select.
Othen changed the query type to Delete and ran it.
Thank for your valuable input.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here