Tech Support Guy banner
Status
Not open for further replies.

Query to tick a yes/no box if a field contains a duplicate value [Access2010]

2K views 13 replies 2 participants last post by  OBP 
#1 ·
Hi all,

Right I have a table, which is the following:


ID - Autonumber
ID2 - Number
ID3 - Number
Duplicate? - Yes/No
Name - Text

Sample Data:

Code:
ID ID2 ID3 Dup? Name
1  1   1   -    Alan Smith
2  2   2   -    Bob Smith
3  3   3   -    Bob Smith
Data I want:
Code:
ID ID2 ID3 Dup? Name
1  1   1   N    Alan Smith
2  2   2   Y    Bob Smith
3  3   3   Y    Bob Smith
You can ignore the ID's - they are not all the same as in the sample data, but they are irrelevant for what I need.

As you will see from the data above, "Alan Smith" is a unique name, and therefore Duplicate? is set to NO; whereas "Bob Smith" has two occurrences, so the Duplicate? is set to YES.

I know I need an append to go through each line to check if it is a duplicate. Any thoughts on how I can go about this???
 
See less See more
#5 ·
The VBA is:

Code:
Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    Dim rs As Object, recount As Integer, count As Integer, rstable As Object, recount2 As Integer, count2 As Integer
    DoCmd.OpenQuery "ITCalls Query"
    Set rs = CurrentDb.OpenRecordset("Find duplicates for LinkedTable")
    rs.MoveLast
    rs.MoveFirst
    recount = rs.RecordCount
    Set rstable = CurrentDb.OpenRecordset("LinkedTable")
    rstable.MoveLast
    rstable.MoveFirst
    recount2 = rstable.RecordCount
    For count = 1 To recount
        For count2 = 1 To recount2
            If rstable.[PossibleDuplicate?] = 0 Then
            'MsgBox
                If rstable.names = rs.[FirstOfnames] Then
                With rstable
                    .Edit
                    ![PossibleDuplicate?] = -1
                    .Update
                    .Bookmark = .LastModified
                End With
                End If
            End If
            rstable.MoveNext
        Next count2
        rstable.MoveFirst
        rs.MoveNext
    Next count
    rs.Close
    rstable.Close
    Set rs = Nothing
    Set rstable = Nothing
MsgBox "Linked Table Updated"
DoCmd.Close

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub
 
#8 ·
Ok so in the attached data, I would expect (after the VBA had run) to have the following data:

Code:
ID	UserID	CallID	PossibleDuplicate?	names	
29	34345	9888	False	                colin  cashmore		
30	41926	9889	True                 	john  phillips		
31	42557	9889	True                 	john  phillips			
32	42611	9889 True                 	john  phillips			
33	53105	9890	False	                roger  betterton			
34	48932	9891	False                	neil  kitchener			
35	33201	9892	False	                ceri  hughes		
36	50971	9893	False                	peter  baldwin		
37	43531	9894	False	                katie  appleyard			
38	34263	9895	False	                clive  reeve		
39	33972	9896	False	                christopher  pawley		
40	35775	9897	True 	                david  evans			
41	35996	9897	True	                david  evans		
42	35475	9897	True                	david  evans
Even though they have different UserID's they are associated with the same call, and have the same name, and as such may be a possible duplicate.
 

Attachments

Status
Not open for further replies.
You have insufficient privileges to reply here.
Top