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
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
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.
ocmd.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?
HAs 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.
Othen 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.