Delete duplicate records daily based on Date

[Brilstern]

I upload names into a database everyday and sometimes I am given duplicate names or names that have previously been submitted. Basically I want to delete all but the latest record based off of the date. I have seen many post about this but I haven't really been able to get anything to work. On my table [Inbound] I have fields [NAME], [SSN], [RANK], [ULN], and [DATE]. I want to delete all duplicate records that have the same SSN excluding Null values. The catch is I want to delete records with the older date. I understand the Min/Max setting on a delete query but I am having trouble getting it to work properly. I figure I am way off so I am starting over. Below is a page that I read with some details regarding the issue but I don't understand access enough to get what I need out of it.

http://bytes.com/topic/access/answers/551798-delete-duplicate-records

NeoPa,

I think your post might have the closest answer but I am not sure where to go with it.

Sgt B

[Stewart Ross]

The example below uses two subqueries, one to match the SSN and the other to exclude rows containing the maximum date for that SSN. Please replace the table name 'tblTest' in the example with the real name of the table concerned.

DELETE 
  *
FROM  
  tblTest AS X
WHERE 
 (X.SSN In 
    (Select 
       SSN 
     FROM 
       tblTest as A 
     WHERE 
       A.SSN=X.SSN
     )
 ) 
AND 
 (X.Date Not In 
    (Select 
       Max([Date]) as MaxDate 
     FROM 
       tblTest as A 
     WHERE 
       A.SSN=X.SSN
     )
 );

Test data:

NAME     SSN    RANK  ULN  DATE
J Bloggs 10101  Sgt   NK   28/12/2011
J Bloggs 10101  Sgt   NK   05/12/2011
J Bloggs 10101  Sgt   NK   03/11/2011
J Bloggs 10101  Sgt   NK   01/11/2011
M Mouse  20000  Lt    A    01/11/2011
M Mouse  20000  Lt    A    02/11/2011
M Mouse  20000  Lt    A    03/11/2011
M Mouse  20000  Lt    A    04/11/2011
M Mouse  20000  Lt    A    05/11/2011
M Mouse  20000  Lt    A    06/11/2011
S Clause 16001  Pt    B    28/12/2011

After deletion:

NAME     SSN    RANK  ULN  DATE
J Bloggs 10101  Sgt   NK   28/12/2011
M Mouse  20000  Lt    A    06/11/2011
S Clause 16001  Pt    B    28/12/2011

-Stewart

[sierra7]

Stewart,
A nice piece of code! I could never get this correlated sub-query stuff to work in Access, so well done!

Stevan,
Personally I hate deleting and would sooner not add the data to create a duplicate in the first place. I would sooner loop through the INCOMING and IF the SSN already existed in the main data then do an UPDATE command, else do the APPEND.

I'm also surmising that there may be subordinate (related) records associated with the table that holds [Name],[SSN],[Rank]... If you link via [SSN] then ok but a US SSN is nine digits and two dashes, i.e. an 11 character string, not what I would choose as a key field to link to other tables. If they were linked via a Long Integer (autonumber) field then the subordinated data would be orphaned when the earlier record was deleted.

But perhaps other people just have a simple life!
S7

[NeoPa]

Generally I would agree with S7 when they say :
"Personally I hate deleting and would sooner not add the data to create a duplicate in the first place. I would sooner loop through the INCOMING and IF the SSN already existed in the main data then do an UPDATE command, else do the APPEND."

In your situation though, where items to be removed from the overall data can come from both sources (previously good data as well as new incoming data), then I'd be inclined to do the import first and then tidy up in a single table.

The following SQL, which is on very similar lines to Stewart's mind you, should do the trick for you :

DELETE *
FROM   [Inbound] AS tIo
WHERE  ([SSN] Is Not Null)
  AND  ([Date] < (SELECT Max([Date])
                  FROM   [Inbound] AS tIi
                  WHERE  (tIi.SSN = tIo.SSN)))

PS. Your situation is quite different from the one in the other thread. There the question asked specifically for a VBA solution. Yours doesn't require such a stipulation and the SQL approach would be far preferable (IMHO).

[Brilstern]

Thank you all, I personally prefer VBA as I understand it better, SQL just throws me for a loop for some reason. (I am currently reading Access for Dummies) I currently have the code below and realized I was deleting both the original record and the new reason so the last few days have been spent re-consolidating excel sheets to refresh my data. I would like to delete because I am linking this data to MS SharePoint and others use my daily updated roster to track their individual personnel so I need to be able to only have the most current data on the table as well as give them the means to research and sort through the data only on the table. I have multiple queries and other tools on the database but they can only see the two separate tables I update everyday. I only today have found out I need to tweak this a little more and I think I can use one of the above solutions but I haven't had the chance to apply any yet. Even though I want to Keep the newest record I also need to involve the ULN. Basically I need to delete records with both the same SSN and ULN.

Private Sub Command27_Click()
 
 DoCmd.SetWarnings False
    
    'Get confirmation from user
      If vbYes = MsgBox("Are you sure you want to import today's JRC Manifest?", vbYesNo, "Importing") Then
        'Check for file existance Inbound
          If Len(Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Inbound\In.xlsx")) = "0" Then
             'File does not exist, import blank sheet
              DoCmd.RunSavedImportExport "Import-Inblk"
            Else
              'File exists, import
              DoCmd.RunSavedImportExport "Import-In"
          
          End If
     
     
        'Check for file existance outbound
          If Len(Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Outbound\Out.xlsx")) = "0" Then
              'File does not exist, import blank sheet
              DoCmd.RunSavedImportExport "Import-Outblk"
            Else
              'File exists, import
              DoCmd.RunSavedImportExport "Import-Out"
           End If
     
       'Done importing, update screen
         DoCmd.Requery ""
      End If
    'Add Date
        DoCmd.OpenQuery "inbound query"
        DoCmd.OpenQuery "outbound query"
    'Date added update screen
        DoCmd.Requery ""
        
    'Delete Duplicates
        DoCmd.OpenQuery "Delete Dup SSN for Inbound", acViewNormal, acEdit
        DoCmd.OpenQuery "Delete Dup SSN for Outbound", acViewNormal, acEdit
    'Date added update screen
        DoCmd.Requery ""
        
 DoCmd.SetWarnings True
        
    End Sub

The below snipit is what I need to edit because this is what is messing up my data. I am trying to sub in something similar to the query NeoPa has provided but with the slight change. Ill get back on after I experiment a little. I appreciate all the help and the feedback.

'Delete Duplicates
        DoCmd.OpenQuery "Delete Dup SSN for Inbound", acViewNormal, acEdit
        DoCmd.OpenQuery "Delete Dup SSN for Outbound", acViewNormal, acEdit

Sierra7,

Not to worried about primary keys as SSN is not my primary key but it does have a auto ID via SharePoint. After you link a table to SharePoint you lose the choice and it creates an Auto ID.

Sgt B

[Brilstern]

Ok, after all said and done I am trying this (currently running). Hope it works. Thx NeoPa.

    DELETE *
    FROM   [Inbound] AS tIo
    WHERE  ([SSN] Is Not Null)
      AND  ([Date] < (SELECT Max([Date])
                      FROM   [Inbound] AS tIi
                      WHERE  (tIi.SSN = tIo.SSN)
                      AND    (tIi.ULN = tIo.ULN)))

Also thx to everybody else. all input is very helpful to a learning access dummy!

Sgt B

** And of course this is pertaining about 50000 records so it is taking forever so it may be tomorrow before I give any feedback, sorry guys **

[Brilstern]

Ok NeoPa, This worked great but runs very slow. Not really sure why but it takes forever!! But I appreciate it. I am trying to add in another stipulation but I'm not really sure how to word it, currently playing with it but i thought I might ask anyways in case I cant find it. Instead of the ULN criteria I want it to see if the dates are less then 10 days apart. Is so delete the older one, if not do nothing. something kinda like this but obviously in correct SQL format which I don't know how to do.

    DELETE *
    FROM   [Inbound] AS tIo
    WHERE  ([SSN] Is Not Null)
      AND  ([Date] < (SELECT Max([Date]) {date - 10}??
                      FROM   [Inbound] AS tIi
                      WHERE  (tIi.SSN = tIo.SSN)

[NeoPa]

Some good thinking there Sgt. The reason this is taking so long is because the subquery is run once for every record it processes. That's 50,000+ queries trying to run. It'll take a while I would guess. A faster-running alternative would involve JOINing two recordsets together via the PK, and would require more knowledge of your table design than we currently have, as well as a fair bit of careful consideration.

For now, why not try out this amended query to handle your ten days grace (NB. For something like this it is critical that you test that it will do what you want prior to running it on the live copy of your data. What we offer are guesses from a distance without the benefit of a full understanding of your database.) :

DELETE *
FROM   [Inbound] AS tIo
WHERE  ([SSN] Is Not Null)
  AND  ([Date] < DateAdd('d', -10,
                         (SELECT Max([Date])
                          FROM   [Inbound] AS tIi
                          WHERE  (tIi.SSN = tIo.SSN)
                          AND    (tIi.ULN = tIo.ULN))))

[sierra7]

Hi
Quote "** And of course this is pertaining about 50000 records so it is taking forever so it may be tomorrow before I give any feedback, sorry guys **"

I think this emphasises the need to clean-out the existing data but make provision for future additions not to cause duplicates.

Ofcourse, if we were dealing with the British army we would have much smaller numbers !-)
S7

[Brilstern]

NeoPa,

The provided Query seams to be more what I had in mind. (noting that the ULN verification is no longer needed due to the 10 day window)Still pretty new at this so I am not sure what all you need to get a better understanding of my database but Ill give a basic description.

Every Morning I get two emails with two different excel sheets in them. From the email I drop them into the "Inbound:" folder and the "Outbound" folder. After that I rename the files "In" and "Out" respectively as well as the first tab in the sheet. From there I used a DoCmd.RunSavedQ uery to amend these two roster to the two table's name "Inbound" and "Outbound". After that it will auto add the date with a simple amend query and then from there is where I want it to look for and delete duplicate entries via the SSN being submitted more than once within the 10 day window. {{//the reason for this is because I get these rosters as they manifest not as they actually fly so I get the same names three days in a row sometimes depending on how hard it is to get out. I am only wanting to capture the last day (or the day they flew)// the 10 day window is to keep from deleting those people that have manifested for different reasons in the past and are now coming again after leaving//}} Below is the VBA i have used to do this. The delete queries are where I will insert the SQL to get the correct outcome.

Private Sub Command27_Click()
     
     DoCmd.SetWarnings False
     
        'Get confirmation from user
          If vbYes = MsgBox("Are you sure you want to import today's JRC Manifest?", vbYesNo, "Importing") Then
            'Check for file existance Inbound
              If Len(Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Inbound\In.xlsx")) = "0" Then
                 'File does not exist, import blank sheet
                  DoCmd.RunSavedImportExport "Import-Inblk"
                Else
                  'File exists, import
                  DoCmd.RunSavedImportExport "Import-In"
     
              End If
     
     
            'Check for file existance outbound
              If Len(Dir("\\lnknfs01\share\rcsw\c-1\0-II MEF (FWD) (1 MAR 2011)\0-Manpower Section\JRC Manifest\Outbound\Out.xlsx")) = "0" Then
                  'File does not exist, import blank sheet
                  DoCmd.RunSavedImportExport "Import-Outblk"
                Else
                  'File exists, import
                  DoCmd.RunSavedImportExport "Import-Out"
               End If
     
           'Done importing, update screen
             DoCmd.Requery ""
          End If
        'Add Date
            DoCmd.OpenQuery "inbound query"
            DoCmd.OpenQuery "outbound query"
        'Date added update screen
            DoCmd.Requery ""
     
        'Delete Duplicates
            DoCmd.OpenQuery "Delete Dup SSN for Inbound", acViewNormal, acEdit
            DoCmd.OpenQuery "Delete Dup SSN for Outbound", acViewNormal, acEdit
        'Date added update screen
            DoCmd.Requery ""
     
     DoCmd.SetWarnings True
     
        End Sub

Does that help you understand the goal any better?

** There are no relationships as this is a SharePoint linked Database, in which it doesn't allow relationships. It also adds an auto ID but I do not use it at all. It is hidden. **

Sgt B

[Brilstern]

Sierra7,

I understand your thought here but that causes an issue seeing as to I want it to delete the old record for several reasons.

1. The new record might have an updated ULN or Unit
2. I want to keep the ID order in order as best as possible for future ideas.
3. In some cases I need to keep duplicates in the case that they came were manifested at a much earlier date and have left and came back.

That is why I need it to add all data and then make changes based on the data all at once.

Sgt B

[NeoPa]

Actually Stevan, I was thinking more of the meta-data (info about the layout / structure) for the table, and within that particularly, the PK. Meta-data can be posted as illustrated by the following example :
Table Name=[tblStudent]

[I]Field           Type      IndexInfo[/I]
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine (Remembering of course, that BB Code takes up no space in the resultant post).

[Brilstern]

ok still a little lost but here is what I understand from your post


Table Name=[Inbound]

[i]Field             Type[/i]
NAME              Text
SSN               Text 
RANK              Text   
ULN               Text
UNIT              Text
DATE              Date/Time    
MONTH             Text

No PK or FK


**this is for my test table i am currently working, as soon as i relink up with SharePoint it adds like 10 extra field...**
Sgt B

[NeoPa]

Stevan:
It also adds an auto ID but I do not use it at all. It is hidden.

Your post #10 seemed to indicate the table had a PK that was added for you.

NeoPa:
A faster-running alternative would involve JOINing two recordsets together via the PK,

As I indicated in post #8, the PK would be a requirement for exploring a solution based on joining the table to itself.

[Brilstern]

Table Name=[Inbound]

[i]Field                    Type[/i]
ID                       AutoNumber
NAME                     Text
SSN                      Text 
RANK                     Text   
ULN                      Text
UNIT                     Text
DATE                     Date/Time    
MONTH                    Text
CONTENT TYPE             Text
FILE TYPE                Text
ATTACHMENTS              Attachments
WORKFLOW INSTANCE ID     Text
MODIFIED                 Date/Time
CREATED                  Date/Time
CREATED BY               Number
MODIFIED BY              Number
URL PATH                 Text
PATH                     Text
ITEM TYPE                Text
ENCODED ABSOLUTE URL     Text

This what it looks like after I upload it to SharePoint. Please keep in mind I cannot create relationships nor can I edit the fields that SharePoint added. Unfortunately SharePoint limits MS Access a lot.