Checking for Dups

[d9pierce]

Good afternoon all,
I have a db and I would like to add a feature to it that checks for
duplicates prior to saving a new record. This would have to check for
the (ClientName), (Address), and (Phone) and would like to somehow
have this pop up to similar names, addresses, or phone numbers that
are current in the duplicate check. I would also like to add an
optional buttons in message that let one OK or cancel if any records
found that match any of the criteria above. It’s mainly to let the
user know that one or more of the criteria consist in another record.

The issue I have with this is when I look over records to maintain
them, I find items such as:
Total Interiors 211 Way street 333-444-5555
Total Iteriors 211 Way street 333-444-5555
The second one is same company just spelled wrong.
And sometimes I have such as this:
Excel Demo 333 Park Ave 111-111-1111
F&A Sales 333 Park Ave 211-222-2222
Which this is OK as it has two businesses from same address but I want
user to be aware of this in case the address is actually wrong. Just a
second glance prior to saving a record.

Can anyone give me an example of this? I would like to use just a
check box on form to activate this feature. I would think this would
be simple however I am lost in doing so. Using Access 2003.

Thanks to all!
(e-mail address removed)

 

[Paul Shapiro]

Good afternoon all,
I have a db and I would like to add a feature to it that checks for
duplicates prior to saving a new record. This would have to check for
the (ClientName), (Address), and (Phone) and would like to somehow
have this pop up to similar names, addresses, or phone numbers that
are current in the duplicate check. I would also like to add an
optional buttons in message that let one OK or cancel if any records
found that match any of the criteria above. It’s mainly to let the
user know that one or more of the criteria consist in another record.

The issue I have with this is when I look over records to maintain
them, I find items such as:
Total Interiors 211 Way street 333-444-5555
Total Iteriors 211 Way street 333-444-5555
The second one is same company just spelled wrong.
And sometimes I have such as this:
Excel Demo 333 Park Ave 111-111-1111
F&A Sales 333 Park Ave 211-222-2222
Which this is OK as it has two businesses from same address but I want
user to be aware of this in case the address is actually wrong. Just a
second glance prior to saving a record.

Can anyone give me an example of this? I would like to use just a
check box on form to activate this feature. I would think this would
be simple however I am lost in doing so. Using Access 2003.

Thanks to all!
(e-mail address removed)

I don't think this is simple at all, for exactly the reasons you already
pointed out. Access can easily find exact matches, but what you want to find
are fuzzy matches, meaning the human can tell they're the same even though
they look different, but that's not so easy for the computer. There are some
algorithms for estimating similarity of text strings, but I've usually given
up rather than try programming that feature.

A couple of alternative ways you can go about it are:
1. Address matching software. This kind of software tries to take any
entered address (and sometimes phone numbers with it) and match it to a
postal address database, giving you clean names and addresses. You can
search on "address correction software" and find a number of options. Years
ago I used this one which was relatively inexpensive for USA addresses:
http://www.melissadata.com/. Inexpensive is a relative term, because prices
went up very fast starting at about USA $1,000 and going to the tens of
thousands of dollars. Maybe the category is cheaper today with a web
services interface? I also looked at a German company's Address Doctor
product, which covered most of the world. Pricing was higher, but the
software looked better at the time.

2. SQL Server Integration Services has a fuzzy matching implementation which
does a very good job once you get it tuned for your data, which was not very
difficult. If you have SQL Server available, this can be a very inexpensive
solution. If not, you can test a downloadable trial to see if it works for
your needs.

3. Settle for something simpler. Show a listbox with companies sorted by
phone number, highlighting the current data, and another listbox sorted by
address, and one by name, etc. Let the user decide if any look like
duplicates.

 

[d9pierce]

Some how I seamed to miss something here. I dont want to check the
address with a different database, I want it to check the current db
that I am using. What I am finding is that when entering data, some
users input things differently or misspell and I have two to ten of
the same company which is just a waste of space. In one situation I
had 9 of the same companies entered but spelled differently or missing
one number in a (phone) txtbox for instance. They start to add a new
record without any means of checking if we have one all ready. I just
want something to check records before a new record is added that does
not meet any of the criteria typed in. If the name is spelled
different ubt phone matches, then do we want this version? Most likely
not but we could if its a company that has different names with the
same phone number? It happens! I am just looking for something that I
can add some code to that will check for duplicates in certain fields
and if a match is found in one or more of them, then it stops with a
message box and a way to view that record before proceeding and adding
an unnecessary record. Does this make any sence? Please let me know
and thanks
Dave

 

[Mike Painter]

Some how I seamed to miss something here. I dont want to check the
address with a different database, I want it to check the current db
that I am using. What I am finding is that when entering data, some
users input things differently or misspell and I have two to ten of
the same company which is just a waste of space. In one situation I
had 9 of the same companies entered but spelled differently or missing
one number in a (phone) txtbox for instance. They start to add a new
record without any means of checking if we have one all ready. I just
want something to check records before a new record is added that does
not meet any of the criteria typed in. If the name is spelled
different ubt phone matches, then do we want this version? Most likely
not but we could if its a company that has different names with the
same phone number? It happens! I am just looking for something that I
can add some code to that will check for duplicates in certain fields
and if a match is found in one or more of them, then it stops with a
message box and a way to view that record before proceeding and adding
an unnecessary record. Does this make any sence? Please let me know
and thanks
Dave

You didn't miss anything.
Access can't tell you that a user should have entered 555-1212 when 555-2121
was entered.
It can't tell you that Smith and Smit are the same person or that E 4 St and
East fourth Street are the same.
Third party, and as mentioned, expensive software can do this. IF you do a
lot of mailing this can pay for itself.

If this is a serious problem there are a few things you can do to help but
at the cost of slowing down the user.
You can ask that they look up the party before they enter a new item.
A combo box based on a phone number is probably best but it could be a name
etc.
You could mandate entering everything twice and asking for a correction if
there is a difference.
You could use "Like" to look up similar names.
You could add a Soundex field for the same purpose.

This is, by and large a human problem and only eyeball version 1.0 will
solve it.

 

[Mark Andrews]

I was doing a similar thing for my software, I ended up going with a simple
concept of:
- check for contacts with a similar address, and if any exist show them on a
popup form.

If you want to get into fuzzy logic:
http://www.kdkeys.net/forums/thread/6450.aspx
http://www.kdkeys.net/forums/1/6432/ShowThread.aspx
http://www.planet-source-code.com/U...Code!asp/txtCodeId!71575/lngWId!1/anyname.htm

For the one I think you need to signup to get the zip file (but it's the
best I could find).

I ended up saying "I'll pass" on fuzzy logic and just went with something
simple.

frmDuplicateAddresses is a simple form just showing addresses.

My database allows for multiple addresses for each contact.

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

Public Sub CheckForDuplicateAddresses(AddressID As Long, AddressStreet1 As
String, AddressCity As String, AddressStateID As Long, AddressZip As String)
On Error GoTo Err_CheckForDuplicateAddresses
'This routine check to see if the address info passed in has any other
addresses which are similar and
'could suggest that the user is entering a duplicate contact/address
'to be similar it has to match:
' exactly to street1
' and exactly to at least one of (city, state, or zip)
Dim RS As DAO.Recordset
Dim sql As String
Dim qd As DAO.QueryDef

sql = "SELECT tblContact.DisplayName, tblAddress.AddressStreet1,
tblAddress.AddressCity, tblAddress.AddressStateID, tblAddress.AddressZip " &
_
"FROM tblContact INNER JOIN tblAddress ON tblContact.ContactID =
tblAddress.ContactID " & _
"WHERE (tblAddress.AddressID <> " & AddressID & ") AND
(tblAddress.AddressStreet1 = """ & AddressStreet1 & """) AND " & _
" ((Nz(tblAddress.AddressCity,'') = """ & AddressCity & """)
OR (tblAddress.AddressStateID = " & CStr(AddressStateID) & ") OR
(Nz(tblAddress.AddressZip,'') = """ & AddressZip & """));"
Set RS = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
If Not (RS.BOF And RS.EOF) Then
Set qd = CurrentDb.QueryDefs("qryDuplicateAddresses")
qd.sql = sql
qd.Close
DoCmd.OpenForm "frmDuplicateAddresses", acNormal, , ,
acFormReadOnly, acDialog
End If
RS.Close

Exit_CheckForDuplicateAddresses:
Set RS = Nothing
Exit Sub

Err_CheckForDuplicateAddresses:
MsgBox Err.Description
Resume Exit_CheckForDuplicateAddresses

End Sub