Delete query to remove specific records based on a date field

[anasttin]

Hi all,



Wondering if someone can assist with a delete query I need to write.

I have a table in MS Access similar to the example shown below (TBL_PRICES) and need to delete records based on the DATE field.

For each unique PRODUCT, I need to:

• Keep all records for that PRODUCT value with a DATE on or after 8/12/16 plus
• Keep the record for each PRODUCT value with the date that is closest but earlier than 8/12/16

All other records should be deleted and the "Action" column to the right of TBL_PRICES indicates which records I want to delete as an example.

Any help would be greatly appreciated.


TBL_PRICES
----------------------------------------

DATE	|	PRODUCT	|	PRICE			Action
1/01/2017	|	widgets	|	1.54			keep
17/12/2016	|	taps	|	1.85			keep
5/12/2016	|	taps	|	1.33			keep
15/11/2016	|	widgets	|	1.24			keep
5/11/2016	|	taps	|	1.13			delete
3/11/2016	|	widgets	|	1.12			delete

[ssanfu]

I couldn't get a query to delete the correct records, so I brute forced it.

Create a standard module and paste in the following code. There needs to be a PK field - I used "ID". (3 places - in RED)

Public Sub DeleteDates()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim s As DAO.Recordset
    Dim sSQL As String
    Dim knt As Integer

    Set d = CurrentDb

    'get the unique products
    sSQL = "SELECT DISTINCT TBL_PRICES.Product FROM TBL_PRICES;"
    ' Debug.Print sSQL
    Set s = d.OpenRecordset(sSQL)
    s.MoveFirst
    Do While Not s.EOF
        knt = 0   'loop counter

        'get the dates by product that are less than #12/08/2016#" (mm/dd/yyyy)
        sSQL = "SELECT  TBL_PRICES.ID, TBL_PRICES.Product, TBL_PRICES.PriceDate"
        sSQL = sSQL & " FROM TBL_PRICES"
        sSQL = sSQL & " WHERE TBL_PRICES.Product = '" & s!product & "' And"
        sSQL = sSQL & " TBL_PRICES.PriceDate < #12/08/2016#"    ' (mm/dd/yyyy)
        sSQL = sSQL & " ORDER BY TBL_PRICES.PriceDate DESC;"
        '  Debug.Print sSQL
        Set r = d.OpenRecordset(sSQL)
        If Not r.BOF And Not r.EOF Then
            Do While Not r.EOF
                If knt > 0 Then  'skip the first record
                    'delete the record
                    sSQL = "DELETE * FROM TBL_PRICES WHERE ID = " & r!ID & ";"
                    d.Execute sSQL, dbFailOnError
                End If

                knt = knt + 1    'increment loop counter

                r.MoveNext
            Loop
        End If

        s.MoveNext
    Loop

    'clean up
    r.Close
    s.Close
    Set r = Nothing
    Set s = Nothing
    Set d = Nothing

    MsgBox "Done"
End Sub

NOTE: I am using date format (mm/dd/yyyy)

TRY this on a COPY of your dB!!!

To execute the code, click anywhere in the code, then press the F5 button... (or F8 if you want to single step through the code to see what happens)

[anasttin]

Hi Ssanfu,

Thanks so much for the code and the time it took you to write it. I'll give it a go.

Much appreciated.

I couldn't get a query to delete the correct records, so I brute forced it.

Create a standard module and paste in the following code. There needs to be a PK field - I used "ID". (3 places - in RED)

Public Sub DeleteDates()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim s As DAO.Recordset
    Dim sSQL As String
    Dim knt As Integer

    Set d = CurrentDb

    'get the unique products
    sSQL = "SELECT DISTINCT TBL_PRICES.Product FROM TBL_PRICES;"
    ' Debug.Print sSQL
    Set s = d.OpenRecordset(sSQL)
    s.MoveFirst
    Do While Not s.EOF
        knt = 0   'loop counter

        'get the dates by product that are less than #12/08/2016#" (mm/dd/yyyy)
        sSQL = "SELECT  TBL_PRICES.ID, TBL_PRICES.Product, TBL_PRICES.PriceDate"
        sSQL = sSQL & " FROM TBL_PRICES"
        sSQL = sSQL & " WHERE TBL_PRICES.Product = '" & s!product & "' And"
        sSQL = sSQL & " TBL_PRICES.PriceDate < #12/08/2016#"    ' (mm/dd/yyyy)
        sSQL = sSQL & " ORDER BY TBL_PRICES.PriceDate DESC;"
        '  Debug.Print sSQL
        Set r = d.OpenRecordset(sSQL)
        If Not r.BOF And Not r.EOF Then
            Do While Not r.EOF
                If knt > 0 Then  'skip the first record
                    'delete the record
                    sSQL = "DELETE * FROM TBL_PRICES WHERE ID = " & r!ID & ";"
                    d.Execute sSQL, dbFailOnError
                End If

                knt = knt + 1    'increment loop counter

                r.MoveNext
            Loop
        End If

        s.MoveNext
    Loop

    'clean up
    r.Close
    s.Close
    Set r = Nothing
    Set s = Nothing
    Set d = Nothing

    MsgBox "Done"
End Sub

NOTE: I am using date format (mm/dd/yyyy)

TRY this on a COPY of your dB!!!

To execute the code, click anywhere in the code, then press the F5 button... (or F8 if you want to single step through the code to see what happens)

[Micron]

is this a one-time thing?
If so, you can sort the table by product, then filter by the dates. Delete all where date => 08/12/2016. Then choose the rows that meet the second condition - unless there's way too many records.

[anasttin]

Thanks for the thought Micron but its not a one time thing and I'm looking at nearly 900K records so Excel has a hissy fit.

Have a good weekend everyone and thanks for the help.

is this a one-time thing?
If so, you can sort the table by product, then filter by the dates. Delete all where date => 08/12/2016. Then choose the rows that meet the second condition - unless there's way too many records.

[Micron]

Just to be clear, I wasn't suggesting Excel. This is also possible on an Access table, although I'm not sure I'd attempt it on that many records. What I found interesting is that Access ignores the records in between the filtered ones and doesn't delete them.

[anasttin]

Thanks Micron.

Just to be clear, I wasn't suggesting Excel. This is also possible on an Access table, although I'm not sure I'd attempt it on that many records. What I found interesting is that Access ignores the records in between the filtered ones and doesn't delete them.