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???
It was not done with the Query, it was done using VBA. It should work all the time the name is the same.
Can you provide me with what doesn't work so that I can check it?
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
And in the linked table it has only checked the box for the first lot of duplicated,, So Adam Brown is checked, but the next duplicated, Adam Charles, isn't
You only need to look at the Button's VBA to see the code.
I could try a different approach, only loading the records with the duplicate name each time, but it may run slower, do you want to try it?
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!