Recordset

[barbapapa]

I have a table that contains: CustomerName, Add Nm1, Add Nm2, Add Ln1, Add
Ln2, City, Zip. The same customer can be in the table with a different
address or with the same address and I want to find the ones with the same
address (the duplicates), I cannot use the find duplicates wizard because
it's only based on one field.

So I try using recordset, I made 2 recordsets of my table and want to
compare the first record from my first recordset (rs1) against all the
record in my second recordset (rs2) if no match move to the next record in
rs1 and start all over again if find match then copy record to a third
table. When I run the program in the first pass it found no match, in the
second pass it tells me there is a match between the second customer in rs1
and the third customer in rs2 (but that's not true) then it leaves the inner
loop execute the outer loop then go back to the inner loop and tells me
there is a match between the 3rd customer in rs1 and the 4th customer in
rs2, then it keep doing that. what am I doing wrong? Hope that makes
sense. Thanks.

rs1.MoveFirst
rs2.MoveFirst
rs2.MoveNext

Do Until rs1.EOF

Do Until rs2.EOF

If rs1(1).Value = rs2(1).Value And rs1(2) = rs2(2) And
rs1(3) = rs2(3) And rs1(4) = rs2(4)
And rs1(5) = rs2(5) And rs1(6) = rs2(6) And rs1(7) =
rs2(7) Then

'move the record from rs1 to a third table. Have not
work on that part yet

rs2.MoveNext

Else

rs2.MoveNext

End If

Loop

rs1.MoveNext
rs2.MoveFirst

Loop

 

[Tom Wickerath]

I cannot use the find duplicates wizard because

it's only based on one field.

Not true. The Find Duplicates wizard includes a screen that reads "Which fields might contain
duplicate information?". You are not limited to selecting only a single field in this screen.
However, in order to find a match, one or more fields chosen in this screen will need to be
*exact* duplicates. You are likely to miss some entries, since humans tend to enter addresses in
an inconsistent manner. For example, take a look at the first screen that you see when clicking
on Tools > Analyze > Table. You will see supplier names of Pavlova, Ltd. and Pav, Ltd. This
example could have just as easily included addresses such as:

74 Rose St.
74 Rose St (without the period)
74 Rose Street
74 Rose Str
74 Rose Str.
etc.

To the query that the wizard creates, or your recordset method of searching, these are all
different addresses.

How many records do you have in this table? Is it a couple of hundred or several thousand? If
it is a couple of hundred, it will likely be faster to simply sort on CustomerName and examine
the results manually. If you are talking several thousand records, you might be able to achieve
acceptable results by using the Russell Soundex function and experimenting with different values
for the number of encoded characters that the function examines.

Tom
____________________________________


I have a table that contains: CustomerName, Add Nm1, Add Nm2, Add Ln1, Add
Ln2, City, Zip. The same customer can be in the table with a different
address or with the same address and I want to find the ones with the same
address (the duplicates), I cannot use the find duplicates wizard because
it's only based on one field.

So I try using recordset, I made 2 recordsets of my table and want to
compare the first record from my first recordset (rs1) against all the
record in my second recordset (rs2) if no match move to the next record in
rs1 and start all over again if find match then copy record to a third
table. When I run the program in the first pass it found no match, in the
second pass it tells me there is a match between the second customer in rs1
and the third customer in rs2 (but that's not true) then it leaves the inner
loop execute the outer loop then go back to the inner loop and tells me
there is a match between the 3rd customer in rs1 and the 4th customer in
rs2, then it keep doing that. what am I doing wrong? Hope that makes
sense. Thanks.

rs1.MoveFirst
rs2.MoveFirst
rs2.MoveNext

Do Until rs1.EOF

Do Until rs2.EOF

If rs1(1).Value = rs2(1).Value And rs1(2) = rs2(2) And
rs1(3) = rs2(3) And rs1(4) = rs2(4)
And rs1(5) = rs2(5) And rs1(6) = rs2(6) And rs1(7) =
rs2(7) Then

'move the record from rs1 to a third table. Have not
work on that part yet

rs2.MoveNext

Else

rs2.MoveNext

End If

Loop

rs1.MoveNext
rs2.MoveFirst

Loop

 

[barbapapa]

Thank Tom for your help. I tried the query and it works fine for what I
need to do, yes I'm dealing with 9000 records. I don't know why I thought I
could not do that with a query, is it a new feature of access 2002? when i
have a moment I will continue to work on my code because it should work.
Thanks.

 

[Tom Wickerath]

is it a new feature of access 2002?
Specifying multiple fields in the Find Duplicates wizard? No--I just opened up Access 97 and it
also allows one to specify > 1 field.

Glad to hear that you got it working.

Tom
______________________________


Thank Tom for your help. I tried the query and it works fine for what I
need to do, yes I'm dealing with 9000 records. I don't know why I thought I
could not do that with a query, is it a new feature of access 2002? when i
have a moment I will continue to work on my code because it should work.
Thanks.

______________________________

I cannot use the find duplicates wizard because
it's only based on one field.

Not true. The Find Duplicates wizard includes a screen that reads "Which fields might contain
duplicate information?". You are not limited to selecting only a single field in this screen.
However, in order to find a match, one or more fields chosen in this screen will need to be
*exact* duplicates. You are likely to miss some entries, since humans tend to enter addresses in
an inconsistent manner. For example, take a look at the first screen that you see when clicking
on Tools > Analyze > Table. You will see supplier names of Pavlova, Ltd. and Pav, Ltd. This
example could have just as easily included addresses such as:

74 Rose St.
74 Rose St (without the period)
74 Rose Street
74 Rose Str
74 Rose Str.
etc.

To the query that the wizard creates, or your recordset method of searching, these are all
different addresses.

How many records do you have in this table? Is it a couple of hundred or several thousand? If
it is a couple of hundred, it will likely be faster to simply sort on CustomerName and examine
the results manually. If you are talking several thousand records, you might be able to achieve
acceptable results by using the Russell Soundex function and experimenting with different values
for the number of encoded characters that the function examines.

Tom
____________________________________


I have a table that contains: CustomerName, Add Nm1, Add Nm2, Add Ln1, Add
Ln2, City, Zip. The same customer can be in the table with a different
address or with the same address and I want to find the ones with the same
address (the duplicates), I cannot use the find duplicates wizard because
it's only based on one field.

So I try using recordset, I made 2 recordsets of my table and want to
compare the first record from my first recordset (rs1) against all the
record in my second recordset (rs2) if no match move to the next record in
rs1 and start all over again if find match then copy record to a third
table. When I run the program in the first pass it found no match, in the
second pass it tells me there is a match between the second customer in rs1
and the third customer in rs2 (but that's not true) then it leaves the inner
loop execute the outer loop then go back to the inner loop and tells me
there is a match between the 3rd customer in rs1 and the 4th customer in
rs2, then it keep doing that. what am I doing wrong? Hope that makes
sense. Thanks.

rs1.MoveFirst
rs2.MoveFirst
rs2.MoveNext

Do Until rs1.EOF

Do Until rs2.EOF

If rs1(1).Value = rs2(1).Value And rs1(2) = rs2(2) And
rs1(3) = rs2(3) And rs1(4) = rs2(4)
And rs1(5) = rs2(5) And rs1(6) = rs2(6) And rs1(7) =
rs2(7) Then

'move the record from rs1 to a third table. Have not
work on that part yet

rs2.MoveNext

Else

rs2.MoveNext

End If

Loop

rs1.MoveNext
rs2.MoveFirst

Loop