Help on Delete Query

[bchilme]

Good Morning all,

I have a query that pulls insurance claim information and I am trying to find a way to delete records that have duplicate claim numbers but all the other fields of the records can be dynamic. Also I would only like to delete the records when the "Date Assigned" is within 2 days of the first assignment. For example say one claim was assigned on 8/3 and the same one was then assigned again on 8/5. I would only want the record to show for the 8/5 record and delete the record from 8/3. On the flip side if the claim was assigned on 8/3 and then assigned again on 8/6 then I would want to keep both records.

I found this criteria to use on an autonumber field that does what I want but I would need to see how I can incorporate the 2 days or less I mentioned earlier.

DELETE [NCAT Trending Log].*
FROM [NCAT Trending Log]
WHERE [NCAT Trending Log].AutoNum Not In (
      SELECT MAX(AutoNum) 
      FROM [NCAT Trending Log] mt 
      WHERE [Date Assigned]=(
            SELECT MAX([Date Assigned]) 
            FROM [NCAT Trending Log] 
            WHERE [Claim Number]=mt.[Claim Number]) 
      GROUP BY [Claim Number]);

Thanks for any help!

[rpeare]

First, I wouldn't delete records at all, you never know when you may need them, you can mark records (by using a yes/no field or something similar) as 'invalid' so they don't show in your user interface. But if changing the design of the tables/user interface is not possible, I wouldn't do this with a query, I would do it with code.

What happens, per your example if you have the same claim number on 8/3, 8/4, 8/5, 8/6, 8/7, 8/8, 8/9

I am assuming based on your description you would end up with the 8/5 record, the 8/8 record and the 8/9 record but that could be wrong.
What happens if you have two claim records on the same day, how would you go about picking the correct one?
Is there a time stamp on the records you can use?

Consider what deleting will do to your recordset very carefully before you actually do it, it's saved me tons of aggravation just to create a 'void' flag on records when necessary.

[orange]

Welcome back rpeare - haven't seen you/posts for a long time.

I agree with the flag/boolean. We used to call that Shadow Delete.

[rpeare]

I get busy from time to time and have to abandon just about everything

[bchilme]

Hi rpeare thanks for the response. So I would want to keep every record that is more than 2 days out from the previous assignment. So your example would never happen but I would end up with just the 8/9 record. We are making assumptions that the claim was handled by someone only if they have had it for longer than 2 days.

I would be pulling the query the first thing in the morning for the previous day. The table I pull from only has current records so I would only be pulling the most up to date record. The other records would be going to a history table. So that would not be an issue with same day.

Do you have a quick link on the marking records invalid process?

[rpeare]

Simple Example

bchilme.zip

[bchilme]

Awesome this looks like something I can use. Thanks for writing all that out for me. Im still not the best with VBA so ill have to look through it for awhile.

Thanks for your help!

[bchilme]

Hi rpear,

Finally got around to trying this out with my own data and i ran into a couple issues.

Not sure why but the code is marking some records as invalid that dont have duplicate ClaimNumbers. Its only 2/18000 record but i cant seem to find a reason why. Also the code wasnt picking up all the dates that were 2 days or less so i changed the code to < 3 days and now it seems to be picking up duplicates that are 3 days apart. Dates are stored as short dates with no times. They all default to 12:00 AM if using Extended Time so i cant seem to figure this out.

Any idea why this might be happening?

Function CleanUp()Dim db As Database
Dim rst As Recordset
Dim sPrevClaimNum As String
Dim dPrevDate As Date
Dim iPrevPK As Long
Dim sCurrClaimNum As String
Dim dCurrDate As Date
Dim iCurrPK As Long


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM NCATTrendingLog ORDER BY ClaimNumber, DateAssigned")


sPrevClaimNum = rst.Fields("ClaimNumber")
dPrevDate = rst.Fields("DateAssigned")
iPrevPK = rst.Fields("AutoNum")


Do While rst.EOF <> True
    sCurrClaimNum = rst.Fields("ClaimNumber")
    dCurrDate = rst.Fields("DateAssigned")
    iCurrPK = rst.Fields("AutoNum")
    
    
    If sCurrClaimNum = sPrevClaimNum Then
        If dPrevDate + 3 < dCurrDate Then
            dPrevDate = dCurrDate
            iPrevPK = iCurrPK
            rst.MoveNext
        Else
            db.Execute ("UPDATE NCATTrendingLog SET Invalid = -1 WHERE AutoNum = " & iPrevPK)
            sPrevClaimNum = sCurrClaimNum
            dPrevDate = dCurrDate
            iPrevPK = iCurrPK
            rst.MoveNext
        End If
    Else
        If DCount("*", "NCATTrendingLog", "[AutoNum] = " & iCurrPK) = 1 Then
            sPrevClaimNum = sCurrClaimNum
            dPrevDate = dCurrDate
            iPrevPK = iCurrPK
            rst.MoveNext
        Else
            MsgBox "HERE"
        End If
    End If
Loop


rst.Close
Set rst = Nothing
Set db = Nothing


End Function

[rpeare]

The code I gave you was an example that worked with my limited dataset, without knowing what your dataset actually looks like I can't offer much on that level, there may be some additional fields that need to be included in your sort to make it come out correctly. I would just start putting in debug.print statements in likely spots (in spots where the decision is being made whether to 'keep' the record or discard it) and see what is actually going on. If you've got a sample dataset I could work with (just needs to be the the two fields involved in this (claim number and date assigned) in a text file or just a sample access database. You can put in some data to simulate your problem but I'm not going to build a database with 18000 records to test