Check table for like values

  • Thread starter Thread starter hollyylloh
  • Start date Start date
H

hollyylloh

I am trying to do roughly as stated in this code sample. I am looking for
some advice on how to go about this the most efficiently as this will be run
over several thousand records at a time.

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblImported")
Do While Not rs.EOF
rs.Edit

'Here I want to compare each value in the imported table against an existing
table to find possible duplicate values. Something like this:?

SELECT tblImported.Company FROM tblImported
WHERE (((tblImported.Company) Like '*' & [tblContacts]![Company]& '*'));

'Then on each record I want to separate out any entries that might have
similar values. These will be copied to a table for further processing.
Values without similar entries will be copied to a different table. So
something like this:?
'Count results
'If Count is > 1 then
'Copy record to table tblInProcess
'else
'copy record to table tblToExport
Loop
rs.Close

I would like to plan ahead and start with the best plan of action and then
work out the details of the code from there. Your advice would be
appreciated.
 
Create a query that joins tblImported.Company to tblContacts.Company. Any
matches you get are duplicates.

Your example used the Like operator with wildcards. If you really need to do
that, you can switch the new query to SQL View, and edit the SQL statement
from:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company = tblContacts.Comany
to:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company Like "*" & tblContacts.Comany & "*"

If you are trying to do this programmatically, copy the SQL statement above
into your code, i.e.:
Dim strSql As String
strSql = "SELECT ...
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
MsgBox "Duplicates found"
End If
 
Thank you Allen that takes care of that question, thank you. I do have an
additional question that I will post here in case you may be able to help. I
am looking for a way to find more than just duplicates. I am looking for a
way to find near duplicates as well. So for example if I search for "Valley
Swim Park Association" I want an existing value of "Valley Swim Park
Facility" to be returned as a posible duplicate. Any Ideas?

Allen Browne said:
Create a query that joins tblImported.Company to tblContacts.Company. Any
matches you get are duplicates.

Your example used the Like operator with wildcards. If you really need to do
that, you can switch the new query to SQL View, and edit the SQL statement
from:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company = tblContacts.Comany
to:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company Like "*" & tblContacts.Comany & "*"

If you are trying to do this programmatically, copy the SQL statement above
into your code, i.e.:
Dim strSql As String
strSql = "SELECT ...
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
MsgBox "Duplicates found"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hollyylloh said:
I am trying to do roughly as stated in this code sample. I am looking for
some advice on how to go about this the most efficiently as this will be
run over several thousand records at a time.

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblImported")
Do While Not rs.EOF
rs.Edit

'Here I want to compare each value in the imported table against an
existing table to find possible duplicate values. Something like this:?

SELECT tblImported.Company FROM tblImported
WHERE (((tblImported.Company) Like '*' & [tblContacts]![Company]& '*'));

'Then on each record I want to separate out any entries that might have
similar values. These will be copied to a table for further processing.
Values without similar entries will be copied to a different table. So
something like this:?
'Count results
'If Count is > 1 then
'Copy record to table tblInProcess
'else
'copy record to table tblToExport
Loop
rs.Close

I would like to plan ahead and start with the best plan of action and
then work out the details of the code from there. Your advice would
be appreciated.
Click to expand...
Click to expand...
 
This is always thorny, and will require some human scanning, but there are
ways you can connect, e.g by joining:
ON Left(tblImported.Company, 10) LIKE "*" & Left(tblContacts.Comany, 10)
& "*"

For surnames, something like Soundex() may help:
http://allenbrowne.com/vba-Soundex.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hollyylloh said:
Thank you Allen that takes care of that question, thank you. I do have an
additional question that I will post here in case you may be able to help.
I
am looking for a way to find more than just duplicates. I am looking for a
way to find near duplicates as well. So for example if I search for
"Valley
Swim Park Association" I want an existing value of "Valley Swim Park
Facility" to be returned as a posible duplicate. Any Ideas?

Allen Browne said:
Create a query that joins tblImported.Company to tblContacts.Company. Any
matches you get are duplicates.

Your example used the Like operator with wildcards. If you really need to
do
that, you can switch the new query to SQL View, and edit the SQL
statement
from:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company = tblContacts.Comany
to:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company Like "*" & tblContacts.Comany & "*"

If you are trying to do this programmatically, copy the SQL statement
above
into your code, i.e.:
Dim strSql As String
strSql = "SELECT ...
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
MsgBox "Duplicates found"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hollyylloh said:
I am trying to do roughly as stated in this code sample. I am looking
for
some advice on how to go about this the most efficiently as this will
be
run over several thousand records at a time.

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblImported")
Do While Not rs.EOF
rs.Edit

'Here I want to compare each value in the imported table against an
existing table to find possible duplicate values. Something like this:?

SELECT tblImported.Company FROM tblImported
WHERE (((tblImported.Company) Like '*' & [tblContacts]![Company]&
'*'));

'Then on each record I want to separate out any entries that might have
similar values. These will be copied to a table for further processing.
Values without similar entries will be copied to a different table. So
something like this:?
'Count results
'If Count is > 1 then
'Copy record to table tblInProcess
'else
'copy record to table tblToExport
Loop
rs.Close

I would like to plan ahead and start with the best plan of action and
then work out the details of the code from there. Your advice would
be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I can work with that, thank you!

Allen Browne said:
This is always thorny, and will require some human scanning, but there are
ways you can connect, e.g by joining:
ON Left(tblImported.Company, 10) LIKE "*" & Left(tblContacts.Comany, 10)
& "*"

For surnames, something like Soundex() may help:
http://allenbrowne.com/vba-Soundex.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hollyylloh said:
Thank you Allen that takes care of that question, thank you. I do have an
additional question that I will post here in case you may be able to help.
I
am looking for a way to find more than just duplicates. I am looking for a
way to find near duplicates as well. So for example if I search for
"Valley
Swim Park Association" I want an existing value of "Valley Swim Park
Facility" to be returned as a posible duplicate. Any Ideas?

Allen Browne said:
Create a query that joins tblImported.Company to tblContacts.Company. Any
matches you get are duplicates.

Your example used the Like operator with wildcards. If you really need to
do
that, you can switch the new query to SQL View, and edit the SQL
statement
from:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company = tblContacts.Comany
to:
FROM tblImported INNER JOIN tblContacts
ON tblImported.Company Like "*" & tblContacts.Comany & "*"

If you are trying to do this programmatically, copy the SQL statement
above
into your code, i.e.:
Dim strSql As String
strSql = "SELECT ...
Set rs = db.OpenRecordset(strSql)
If rs.RecordCount > 0 Then
MsgBox "Duplicates found"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to do roughly as stated in this code sample. I am looking
for
some advice on how to go about this the most efficiently as this will
be
run over several thousand records at a time.

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblImported")
Do While Not rs.EOF
rs.Edit

'Here I want to compare each value in the imported table against an
existing table to find possible duplicate values. Something like this:?

SELECT tblImported.Company FROM tblImported
WHERE (((tblImported.Company) Like '*' & [tblContacts]![Company]&
'*'));

'Then on each record I want to separate out any entries that might have
similar values. These will be copied to a table for further processing.
Values without similar entries will be copied to a different table. So
something like this:?
'Count results
'If Count is > 1 then
'Copy record to table tblInProcess
'else
'copy record to table tblToExport
Loop
rs.Close

I would like to plan ahead and start with the best plan of action and
then work out the details of the code from there. Your advice would
be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top