WHERE/GROUP BY Clause on Memo Field. 2

[Ed2020]

Hi,

I have a table in MS Access containing a list of Business Objects report files. The table contains a FileName field and an SQL field.

FileName is stored in text and SQL is stored in a memo field (owing to the length of some of the SQL statements).

It seems we have numerous copies of the same file in some instances and I want to flag any duplicates. I believe the best way to do this is to look for duplicates based on FileName and the SQL statement, to avoid incorrectly flagging duplicates on FileName alone.

My problem is that this will require writing a query that either uses a WHERE or GROUP BY clause on the memo field and this doesn't work properly. :-(

Does anyone have any solutions to this?

Many thanks,

Ed Metcalfe.

Please do not feed the trolls.....

 

[dhookom]

You could only compare the first 255 characters of your memo field.

SELECT FileName, Left(SQL, 255) as SQL255, Count(*) as NumOf
FROM ATable
GROUP BY FileName, Left(SQL, 255);

You could possibly use code looping through a recordset that is ordered by FileName and Left(SQL,255). Another possibility would be to push the data to a SQL Server or SQL Server Express table which allows at least 8000 characters in the group by.

Duane
Hook'D on Access
MS Access MVP

 

[Ed2020]

Thanks Duane.

If I used your second suggestion (looping through a recordset) and created this code, which I think resolves the 255 character limit problem and allows me to compare the full contents of the memo field. I'd appreciate it if you could point out any potential flaws in my logic!

Public Sub FindDuplicates()
    Dim ThisDB As DAO.Database
    Dim rstReps1 As DAO.Recordset
    Dim strSQL As String
    Dim rstReps2 As DAO.Recordset
    Dim lngProgress As Long
    
    Set ThisDB = CurrentDb()
    Set rstReps1 = ThisDB.OpenRecordset("tblBOReps", dbOpenDynaset)
    
    ThisDB.Execute "UPDATE tblBOReps SET tblBOReps.Duplicate = False;", dbFailOnError
    
    With rstReps1
        Do While Not .EOF
            strSQL = "SELECT * FROM tblBOReps WHERE FileID<>" & !fileid & ";"
            Set rstReps2 = ThisDB.OpenRecordset(strSQL, dbOpenDynaset)
            Do While Not rstReps2.EOF
                If rstReps2!FileName = !FileName And rstReps2!SQL = !SQL Then
                    .Edit
                        !Duplicate = True
                    .Update
                End If
                rstReps2.MoveNext
            Loop
            lngProgress = lngProgress + 1
            Debug.Print lngProgress & " records processed so far..."
            .MoveNext
        Loop
    End With
    
    rstReps1.Close
    rstReps2.Close
    ThisDB.Close
    
    Set rstReps1 = Nothing
    Set rstReps1 = Nothing
    Set ThisDB = Nothing
    
    MsgBox "Duplicate flagging complete.", vbOKOnly + vbInformation, "Complete."
End Sub

Many thanks,

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

I'd update before opening the recordset:
ThisDB.Execute "UPDATE tblBOReps SET tblBOReps.Duplicate = False;", dbFailOnError
Set rstReps1 = ThisDB.OpenRecordset("tblBOReps", dbOpenDynaset)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ed2020]

Thanks PHV. Good point.

Purple stars to both of you for taking the time to look.

Ed Metcalfe.

Please do not feed the trolls.....

 

[dhookom]

I would also change the inner loop strSQL to:

strSQL = "SELECT * FROM tblBOReps WHERE FileID<>" & !fileid & " AND FileName =""" & !FileName & """ "

Duane
Hook'D on Access
MS Access MVP

 

[Ed2020]

Thanks both, much appreciated.

Ed Metcalfe.

Please do not feed the trolls.....