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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hollyylloh" <(E-Mail Removed)> wrote in message
news:853F0BCF-ACED-4EEA-82F0-(E-Mail Removed)...
>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.

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" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "hollyylloh" <(E-Mail Removed)> wrote in message
> news:853F0BCF-ACED-4EEA-82F0-(E-Mail Removed)...
> >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.
>
>

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"hollyylloh" <(E-Mail Removed)> wrote in message
news:26386CEC-5C9F-4E40-B5AA-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "hollyylloh" <(E-Mail Removed)> wrote in message
>> news:853F0BCF-ACED-4EEA-82F0-(E-Mail Removed)...
>> >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.
>>
>>

I can work with that, thank you!

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "hollyylloh" <(E-Mail Removed)> wrote in message
> news:26386CEC-5C9F-4E40-B5AA-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "hollyylloh" <(E-Mail Removed)> wrote in message
> >> news:853F0BCF-ACED-4EEA-82F0-(E-Mail Removed)...
> >> >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.
> >>
> >>
>
>