a way to prevent duplicate contact records?

[Catherine Jo Morgan]

Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?

Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database? TIA

[Steve Jorgensen]

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan" <cj******@hemc.net>
wrote:

Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?
Yes. In table design, open the indexes Window, and add a new index. To
indicate that it is a single index with multiple fields, enter the fields on
consecutive rows, and leave the index name blank on all but the first row.
With the first row selected, set the Unique property to Yes.
Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database? TIA

Trap the Before Update event on the form, and do a search for existing records
with the same combination of values. I recommend using a parameter query for
the search, so you don't have to worry about checking for quote characters,
etc.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd recommend making a Primary Key instead of a unique index (I know,
same animal, different name) since Primary Keys were meant to do that.

I usually use the Form_Error procedure to trap duplicate record
errors. I've also used the "check before saving" method to do some
fancy work arounds if the user is entering a duplicate record (tell
user the record already exists - asking if they want to see it -
reloading form w/ existing record if they say yes).

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8T5uoechKqOuFEgEQL7nACeMpzzIbcY0FvEicB+rSOQkt nIPcYAn31+
veFAxqLW19tEEOPddyYCRful
=J/gd
-----END PGP SIGNATURE-----

Steve Jorgensen wrote:

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan" <cj******@hemc.net>
wrote:

Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?

Yes. In table design, open the indexes Window, and add a new index. To
indicate that it is a single index with multiple fields, enter the fields on
consecutive rows, and leave the index name blank on all but the first row.
With the first row selected, set the Unique property to Yes.

Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database? TIA

Trap the Before Update event on the form, and do a search for existing records
with the same combination of values. I recommend using a parameter query for
the search, so you don't have to worry about checking for quote characters,
etc.

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<a2********************************@4ax.com>:

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan"
<cj******@hemc.net> wrote:

Can I set it up so that a certain combination of fields can't
contain the same entries, on another record? e.g. a combination
of FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?

Yes. In table design, open the indexes Window, and add a new
index. To indicate that it is a single index with multiple
fields, enter the fields on consecutive rows, and leave the index
name blank on all but the first row. With the first row selected,
set the Unique property to Yes.

Well, theoretically, that's a nice-sounding idea, but in reality,
you'll end up with Nulls in some of the fields, which means you
can't really have a unique index.

Or is it possible to allow this but to throw up an alert message?
Warning that this person is probably already in the database? TIA

Trap the Before Update event on the form, and do a search for
existing records with the same combination of values. I recommend
using a parameter query for the search, so you don't have to worry
about checking for quote characters, etc.

I don't think this kind of thing can be accomplished with indexes
for people records. First off, addresses, phone numbers and email
addresses really oughtn't be stored in the same table as the name,
etc., so you wouldn't have that information available to you until
after you've created the person record. You can't have a unique
index on name, as there are plenty of cases where people share
exactly the same name.

I accomplish this by using an unbound form to add people. When you
enter the first name and last name, you have to click a CHECK FOR
DUPLICATES button before you the ADD>> button is actually enabled.
That reveals a subform that lists close matches (based on various
combinations of LIKE matches and Soundex and Soundex2 matches), and
allows you to look at the close matches and determine if they are
the same person. If so, there's a command button that abandons the
add and takes you to the selected duplicate record.

This works pretty well, but it is dependent on the users actually
reading what's onscreen.

But I don't know any other solution that would not prohibit valid
data entry.

I've been intending to package this up as a sample database for
ages, but just haven't had the time.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

I disagree because 95% of the time, the primary key should be an Autoincrement
field, though as you say, it doesn't matter much which one is the primary key,
and which one is a unique index.

On Wed, 26 Nov 2003 19:06:31 GMT, MGFoster <[e-mail]> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd recommend making a Primary Key instead of a unique index (I know,
same animal, different name) since Primary Keys were meant to do that.

I usually use the Form_Error procedure to trap duplicate record
errors. I've also used the "check before saving" method to do some
fancy work arounds if the user is entering a duplicate record (tell
user the record already exists - asking if they want to see it -
reloading form w/ existing record if they say yes).

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8T5uoechKqOuFEgEQL7nACeMpzzIbcY0FvEicB+rSOQkt nIPcYAn31+
veFAxqLW19tEEOPddyYCRful
=J/gd
-----END PGP SIGNATURE-----

Steve Jorgensen wrote:

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan" <cj******@hemc.net>
wrote:

Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?

Yes. In table design, open the indexes Window, and add a new index. To
indicate that it is a single index with multiple fields, enter the fields on
consecutive rows, and leave the index name blank on all but the first row.
With the first row selected, set the Unique property to Yes.

Or is it possible to allow this but to throw up an alert message? Warning
that this person is probably already in the database? TIA

Trap the Before Update event on the form, and do a search for existing records
with the same combination of values. I recommend using a parameter query for
the search, so you don't have to worry about checking for quote characters,
etc.

[Steve Jorgensen]

On Wed, 26 Nov 2003 21:12:59 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:

no****@nospam.nospam (Steve Jorgensen) wrote in
<a2********************************@4ax.com>:

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan"
<cj******@hemc.net> wrote:

Can I set it up so that a certain combination of fields can't
contain the same entries, on another record? e.g. a combination
of FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?
Yes. In table design, open the indexes Window, and add a new
index. To indicate that it is a single index with multiple
fields, enter the fields on consecutive rows, and leave the index
name blank on all but the first row. With the first row selected,
set the Unique property to Yes.

Well, theoretically, that's a nice-sounding idea, but in reality,
you'll end up with Nulls in some of the fields, which means you
can't really have a unique index.

Doh - right - what he said.
I accomplish this by using an unbound form to add people. When you
enter the first name and last name, you have to click a CHECK FOR
DUPLICATES button before you the ADD>> button is actually enabled.
That reveals a subform that lists close matches (based on various
combinations of LIKE matches and Soundex and Soundex2 matches), and
allows you to look at the close matches and determine if they are
the same person. If so, there's a command button that abandons the
add and takes you to the selected duplicate record.

This works pretty well, but it is dependent on the users actually
reading what's onscreen.

I forgot about this when replying to the message originally, but a similar
idea I picked up from the spec for a system I did recently was that the add
and search screens are the same, and you can't do an add without first doing a
search that must include certain fields. When you then select the new record
option, the search values are filled in as the defaults for the new record.

I guess you could call it a "find-or-add" model.

My implementation of that model had a form split into 3 sections. A short top
section contains the search parameter controls and the "Find" button. The
bottom section is split left/right with the search results listbox on the
left, and an add/edit subform on the right. The listbox is based on a union
query with a <<New Record>> row added at the bottom. The subform uses the
listbox selection as its master link, and on after update of the listbox, the
AllowAdditions property of the subform is set to True of False depending on
whether the new record selection is chosen.

[Phil Stanton]

Could you enlighten me. OK I agree that addresses can well be held in an
address table, but what is the advantage of holding phone nos or email
addresses in another table, unless there is a possibility of multiple phones
or e-mail addresses. Unlikely with a Club other than a mobile and land line.

Phil
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .

no****@nospam.nospam (Steve Jorgensen) wrote in
<a2********************************@4ax.com>:

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan"
<cj******@hemc.net> wrote:

Can I set it up so that a certain combination of fields can't
contain the same entries, on another record? e.g. a combination
of FirstName/LastName/address? Or FirstName/LastName/phone? Or
FirstName/LastName/email?

Yes. In table design, open the indexes Window, and add a new
index. To indicate that it is a single index with multiple
fields, enter the fields on consecutive rows, and leave the index
name blank on all but the first row. With the first row selected,
set the Unique property to Yes.

Well, theoretically, that's a nice-sounding idea, but in reality,
you'll end up with Nulls in some of the fields, which means you
can't really have a unique index.

Or is it possible to allow this but to throw up an alert message?
Warning that this person is probably already in the database? TIA

Trap the Before Update event on the form, and do a search for
existing records with the same combination of values. I recommend
using a parameter query for the search, so you don't have to worry
about checking for quote characters, etc.

I don't think this kind of thing can be accomplished with indexes
for people records. First off, addresses, phone numbers and email
addresses really oughtn't be stored in the same table as the name,
etc., so you wouldn't have that information available to you until
after you've created the person record. You can't have a unique
index on name, as there are plenty of cases where people share
exactly the same name.

I accomplish this by using an unbound form to add people. When you
enter the first name and last name, you have to click a CHECK FOR
DUPLICATES button before you the ADD>> button is actually enabled.
That reveals a subform that lists close matches (based on various
combinations of LIKE matches and Soundex and Soundex2 matches), and
allows you to look at the close matches and determine if they are
the same person. If so, there's a command button that abandons the
add and takes you to the selected duplicate record.

This works pretty well, but it is dependent on the users actually
reading what's onscreen.

But I don't know any other solution that would not prohibit valid
data entry.

I've been intending to package this up as a sample database for
ages, but just haven't had the time.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

Actually, it is not unlikely with a club, especially these days.

On Wed, 26 Nov 2003 23:08:46 -0000, "Phil Stanton" <ph**@stantonfamily.co.uk>
wrote:

Could you enlighten me. OK I agree that addresses can well be held in an
address table, but what is the advantage of holding phone nos or email
addresses in another table, unless there is a possibility of multiple phones
or e-mail addresses. Unlikely with a Club other than a mobile and land line.

Phil
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74. ..

no****@nospam.nospam (Steve Jorgensen) wrote in
<a2********************************@4ax.com>:

>On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan"
><cj******@hemc.net> wrote:
>
>>Can I set it up so that a certain combination of fields can't
>>contain the same entries, on another record? e.g. a combination
>>of FirstName/LastName/address? Or FirstName/LastName/phone? Or
>>FirstName/LastName/email?
>
>Yes. In table design, open the indexes Window, and add a new
>index. To indicate that it is a single index with multiple
>fields, enter the fields on consecutive rows, and leave the index
>name blank on all but the first row. With the first row selected,
>set the Unique property to Yes.

Well, theoretically, that's a nice-sounding idea, but in reality,
you'll end up with Nulls in some of the fields, which means you
can't really have a unique index.

>>Or is it possible to allow this but to throw up an alert message?
>>Warning that this person is probably already in the database? TIA
>
>Trap the Before Update event on the form, and do a search for
>existing records with the same combination of values. I recommend
>using a parameter query for the search, so you don't have to worry
>about checking for quote characters, etc.

I don't think this kind of thing can be accomplished with indexes
for people records. First off, addresses, phone numbers and email
addresses really oughtn't be stored in the same table as the name,
etc., so you wouldn't have that information available to you until
after you've created the person record. You can't have a unique
index on name, as there are plenty of cases where people share
exactly the same name.

I accomplish this by using an unbound form to add people. When you
enter the first name and last name, you have to click a CHECK FOR
DUPLICATES button before you the ADD>> button is actually enabled.
That reveals a subform that lists close matches (based on various
combinations of LIKE matches and Soundex and Soundex2 matches), and
allows you to look at the close matches and determine if they are
the same person. If so, there's a command button that abandons the
add and takes you to the selected duplicate record.

This works pretty well, but it is dependent on the users actually
reading what's onscreen.

But I don't know any other solution that would not prohibit valid
data entry.

I've been intending to package this up as a sample database for
ages, but just haven't had the time.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

ph**@stantonfamily.co.uk (Phil Stanton) wrote in
<3f***********************@mercury.nildram.net>:

Could you enlighten me. OK I agree that addresses can well be held
in an address table, but what is the advantage of holding phone
nos or email addresses in another table, unless there is a
possibility of multiple phones or e-mail addresses. Unlikely with
a Club other than a mobile and land line.

Ha, ha, ha, ha, ha, ha, ha, ha!!!!

Boy, that one had me laughing.

I've not had an app in years that didn't need (note that I store
email and phone in the same table):

Home
Home Fax
Mobile
Home Email
Work
Work 2
Work Fax
Work Email

Some add a second mobile number and a pager number.

That many phone fields onscreen takes up a lot of space. I utilize
an outer join to populate all the possibilities, as in this screen
shot:

http://www.bway.net/~dfassoc/example...ontactInfo.jpg

I find that clients like to see the choices laid out for them
rather than to have to pick from a dropdown list. I also find that
the method used in Outlook, for instance, is difficult, because
most users will never notice that there are other types of numbers
that are recordable.

In that screenshot, there are two subforms, one for phones and one
for email. The phone types are categorized by Office or Phone in a
lookup table linked by PhoneTypeID. The subforms are filtered on
the PersonID and the tag of the subform object. That is, the
subform object for the home phones and home email form controls on
the parent form have a tag "Home" and is linked with:

LinkChildFields = "PhoneType"

The record source uses an outer join to a subquery that filters by
the PersonID of the parent form.

I use this kind of thing in all my apps these days. Adding a phone
type does not require much restructuring at all, just adding to the
phone types table and resizing the subform controls, which is a lot
easier than adding fields, changing recordsources and then finding
enough room for additional fields (though that's pretty much equal
between the two approaches if you lay it out right).

And every client that I've implemented this for has added at least
one phone type after the app was in production, precisely because
they knew it was possible without much trouble.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Catherine Jo Morgan]

Actually, my design puts People, Addresses, and Comms in separate tables,
with junction tables between People/Addresses and People/Comms. I just sort
of took this for granted as the best way to do it. People with PO Box
mailing addresses need a second address for UPS or FedEx shipments. Also
some people have two homes. Re Comms - I'd have a lot of blank fields in
most People records if I tried to add a field for every possible CommType
like email, cellphone, home phone, work phone, etc.

"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message
news:3f***********************@mercury.nildram.net ...

Could you enlighten me. OK I agree that addresses can well be held in an
address table, but what is the advantage of holding phone nos or email
addresses in another table, unless there is a possibility of multiple phones or e-mail addresses. Unlikely with a Club other than a mobile and land line.
Phil
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.74.. .

no****@nospam.nospam (Steve Jorgensen) wrote in
<a2********************************@4ax.com>:

On Wed, 26 Nov 2003 12:30:01 -0500, "Catherine Jo Morgan"
<cj******@hemc.net> wrote:

>Can I set it up so that a certain combination of fields can't
>contain the same entries, on another record? e.g. a combination
>of FirstName/LastName/address? Or FirstName/LastName/phone? Or
>FirstName/LastName/email?

Yes. In table design, open the indexes Window, and add a new
index. To indicate that it is a single index with multiple
fields, enter the fields on consecutive rows, and leave the index
name blank on all but the first row. With the first row selected,
set the Unique property to Yes.

Well, theoretically, that's a nice-sounding idea, but in reality,
you'll end up with Nulls in some of the fields, which means you
can't really have a unique index.

>Or is it possible to allow this but to throw up an alert message?
>Warning that this person is probably already in the database? TIA

Trap the Before Update event on the form, and do a search for
existing records with the same combination of values. I recommend
using a parameter query for the search, so you don't have to worry
about checking for quote characters, etc.

I don't think this kind of thing can be accomplished with indexes
for people records. First off, addresses, phone numbers and email
addresses really oughtn't be stored in the same table as the name,
etc., so you wouldn't have that information available to you until
after you've created the person record. You can't have a unique
index on name, as there are plenty of cases where people share
exactly the same name.

I accomplish this by using an unbound form to add people. When you
enter the first name and last name, you have to click a CHECK FOR
DUPLICATES button before you the ADD>> button is actually enabled.
That reveals a subform that lists close matches (based on various
combinations of LIKE matches and Soundex and Soundex2 matches), and
allows you to look at the close matches and determine if they are
the same person. If so, there's a command button that abandons the
add and takes you to the selected duplicate record.

This works pretty well, but it is dependent on the users actually
reading what's onscreen.

But I don't know any other solution that would not prohibit valid
data entry.

I've been intending to package this up as a sample database for
ages, but just haven't had the time.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc