Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-28-2019, 08:47 AM   #1
lucky245
Newly Registered User
 
Join Date: Sep 2009
Posts: 13
Thanks: 7
Thanked 0 Times in 0 Posts
lucky245 is on a distinguished road
compare tables and delete oldest record from either table

I need to compare a small source table (upto 100 records) with a much larger destination table (20000).

If the ID is the same I need to delete any records (there may be more than one) that are older in the destination table. It doesn't matter if the other fields differ as the newer records will have correct information in them. If the timestamp is identical then I want to delete from source as this table will be appended to the destination table afterwards.

I am going to attempt writing some vba with loops to compare but just in case someone tells me an sql will be faster


Thanks

Current progress but deleting from same table not either

Code:
Option Compare Database

    Sub test()
        DelDupRec "tableBDes"
    End Sub

Code:
Sub DelDupRec(strTableName As String)
  
    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim tdf As TableDef
    Dim strSQL As String
    Dim varBookmark As Variant
 
   Set db = CurrentDb


    Set tdf = db.TableDefs(strTableName)
    strSQL = "SELECT * FROM " & strTableName & " ORDER BY spnumber"
    
    strSQL = Left(strSQL, Len(strSQL))
    Set tdf = Nothing
 
    Set rst = db.OpenRecordset(strSQL)
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        If rst.Fields(1).Value <> rst2.Fields(1).Value Then
            GoTo NextRecord
        Else
            If rst.Fields(2).value > rst2.Fields(2).value Then
                GoTo NextRecord
            End If
        End If
            rst.Delete
            GoTo SkipBookmark
NextRecord:
            rst2.Bookmark = varBookmark
SkipBookmark:
            rst.MoveNext
    Loop
End Sub
Attached Files
File Type: accdb Example.accdb (440.0 KB, 15 views)


Last edited by Uncle Gizmo; 05-30-2019 at 08:05 AM.
lucky245 is offline   Reply With Quote
Old 05-29-2019, 08:02 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,271
Thanks: 527
Thanked 927 Times in 879 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: compare tables and delete oldest record from either table

Quote:
Originally Posted by lucky245 View Post
someone tells me an sql will be faster
As a general rule SQL Statements are normally faster, if not always! I suspect you already know this. As to what the SQL Statement should look like, I have no idea! I normally just prat around with it/them until it does what I want. I guess you will need several queries, one calling the next and so on.

The query Builder supplied by Microsoft Access has some useful features, it can build you an "unmatched query" and a couple of other sorts of queries which you might want to look into. Otherwise seeing as I have just bumped this thread up the list a bit, you might get some help from someone who knows what they are doing!
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
lucky245 (05-30-2019)
Old 05-29-2019, 08:13 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,563
Thanks: 50
Thanked 1,046 Times in 1,027 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: compare tables and delete oldest record from either table

Hi. This could be a toss up, but some testing would definitely let us know for sure. Using VBA, you can complete all the tasks one step at a time, and by the time you get to the last record, then you're all done. Using SQL, you may have to perform the checks one step at a time, but applying each step to the entire table, instead of one record at a time (like VBA could). If you get the "rules" correctly identified, I might still go with multiple SQL queries executing in succession to do the whole job. But, doing it using VBA is not entirely out of the question too.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-29-2019, 08:33 AM   #4
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,024
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: compare tables and delete oldest record from either table

For me, First step would be a query that matches your source table to your destination table by ID. This would have your date/times. You can them work out how to return ONLY those records where your source had a previous date/time to your destination. This becomes your first delete query.

Second step would be to do a match between your destination and source, returning ONLY those where there is a match. Use the same logic for date/time filter to set this up to be a delete query for your destination table.

Third is the append query you'd already talked about.

For myself, I'd actually have the first query not delete, but instead force a user to review the data to make sure your actually using the latest and greatest. The user can make judgement calls about dates and such that are horribly difficult to get right in programming.

I'd also be setting a "Deleted" flag in your destination table rather than actually deleting the records.
Mark_ is offline   Reply With Quote
Old 05-29-2019, 11:37 PM   #5
lucky245
Newly Registered User
 
Join Date: Sep 2009
Posts: 13
Thanks: 7
Thanked 0 Times in 0 Posts
lucky245 is on a distinguished road
Re: compare tables and delete oldest record from either table

This seems to solve the problem. Slight change in approach as any records in input table will always be the most up to date so I only need to remove the records from the destination table with the same spnumber. Can anyone see any potential issues or cleaner code. "I will add error code after"

Code:
Sub DelDupRec(strTableName As String)
  
    Dim db As Database
    Dim rs As Recordset 'destination table
    Dim rs2 As Recordset 'match query
    Dim strSQL As String 'sql for match
    Dim i As Long
    Dim j As Long
    Dim lngdst As Long
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTableName) 'Destination
    strSQL = "SELECT input.spnumber FROM [input] INNER JOIN dest ON input.spnumber = dest.spnumber GROUP BY input.spnumber;"
    Set rs2 = db.OpenRecordset(strSQL) 'match query
    lngdst = rs.RecordCount + 1 ' destination counter
    rs.MoveFirst
    rs2.MoveFirst
    
    For i = 1 To rs2.RecordCount
        rs.MoveFirst
        For j = 1 To lngdst
            If rs2.Fields(0) = rs.Fields(1) Then
                rs.Delete
                lngdst = lngdst - 1
            End If
            rs.MoveNext
        Next j
       rs2.MoveNext
    Next i
End Sub

Last edited by Uncle Gizmo; 05-30-2019 at 08:03 AM. Reason: Added code Tags - (You can do it yourself by pressing #)
lucky245 is offline   Reply With Quote
Old 05-30-2019, 05:19 AM   #6
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,143
Thanks: 27
Thanked 270 Times in 257 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: compare tables and delete oldest record from either table

Do you have an index set on any field? If not try setting an index on "spnumber", if that's the field you are comparing. Just a thought.

AccessBlaster is offline   Reply With Quote
The Following User Says Thank You to AccessBlaster For This Useful Post:
lucky245 (05-30-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Compare 2 tables where one record matches and another doesn't lynxbci General 2 06-26-2013 02:49 AM
Compare 2 tables, then use result to remove items off original Table HaroldIII Queries 1 02-22-2011 02:57 PM
Compare two tables and delete missing data? option Queries 0 01-26-2009 10:56 AM
Value from record w/oldest date within a range thewa Modules & VBA 2 10-10-2005 03:23 PM
Compare tables and delete MarcusAntonius Queries 2 07-15-2004 02:06 AM




All times are GMT -8. The time now is 06:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World