Full Version: Removing (mostly) Redundant Rows In A Table
UtterAccess Forums > Microsoft® Access > Access Queries
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
(Row 2) where Batchno not equal to Ref
Then Delete Row Where Batchno IS equal to Ref
Any help would be marvellous
Bob G
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
Hi Bob G
Thanks for replying
A2010 x32
The row count for the Dup Wizard reports 16k rows
Many thanks
Kind regards
Bob G
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.
ocmd.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?
Bob G
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;"
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
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.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.