Duplicates - How to prevent from TWO columns?

[Tim McGavin]

I currently have a column called "email". I have prevented duplicates from
existing by changing the "Indexed" property to have a "Yes (No Duplicates)"
value.

But I just recently added another column called "email2". How can I prevent
duplicates from existing between both columns together?

In other words, if I enter an email address in "email" and it exists
somewhere in "email2" then I would like for the system to catch that as a
duplicate.

What are the best ways to do this?

Tim

 

[Jeff Boyce]

Tim

One approach would be to use a new table structure.

One of my applications has to handle the situation that one entity (person,
org, agency, ...) can have zero, one or many addresses (and phone numbers,
and email addresses). To manage this requirement, there are three tables
(using your email example):

tblEntity

tblEmailAddress

trelEntityEmailAddress

This third table holds valid combinations of Entity and EmailAddress. This
allows one Entity to have many (or even no) email addresses, and one
EmailAddress to be used by many Entities (not so necessary anymore, with the
proliferation of free emailer ISPs, but each/every person in a family may
still use the same generic email address).

If you index the trelEntityEmailAddress columns to prevent duplicate Entity
X EmailAddress combinations, this would achieve your requirement as I
understood it (i.e., no Entity should have duplicate email addresses).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Tim McGavin]

I'm sure I understand your solution which leads me to believe that maybe I
didn't explain myself very well.

So here goes again...

I have these colums....

email
email2

The first column might contain addresses like this...

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

And the second column might contain the same type of data.

But I want Access to disallow the entry of the same email address in either
column.

In other words, if column "email" contains (e-mail address removed) the I should not be
able to enter (e-mail address removed) into the "email2" column.

Help!

 

[Douglas J. Steele]

You cannot do it at a table level in Access.

The only way to do it is using a form. In the form's BeforeUpdate event,
you'd put code to check whether or not the new value already exists in
either of the two fields.

However, having two such fields, both of which are intended to hold the same
data, suggests that the table hasn't been properly normalized.

 

[John W. Vinson]

I'm sure I understand your solution which leads me to believe that maybe I
didn't explain myself very well.

So here goes again...

I have these colums....

email
email2

The first column might contain addresses like this...

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

And the second column might contain the same type of data.

But I want Access to disallow the entry of the same email address in either
column.

In other words, if column "email" contains (e-mail address removed) the I should not be
able to enter (e-mail address removed) into the "email2" column.

Help!

You cannot do this in a Table. You can use a unique index on a field to
prevent duplicates in that field, but you're paying the price for having
repeated fields: your table design is not normalized, so the tools for
handling normalized data won't work.

The only way I can think of to do this would be to use VBA code in the form's
BeforeUpdate event to use DLookUp on both fields to see if there is a
duplicate, and cancel the event if so. If you're entering data directly into
tables, you're out of luck - and shouldn't be doing so.

John W. Vinson [MVP]

 

[Jeff Boyce]

Tim

As you've seen from a couple other responses, it is not recommended use the
approach you've described. That's why I offered an alternate approach.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/