help!!! duplicate records problem...

[dan]

I have a table called Contacts, which has fields similar
to those shown below:

ContactId
Firstname
Surname
Address
DateOfBirth

Now, in my wisdom I created ContactId as an autonum
primary key. However, this obviously allows duplicate
contact records to be created. That is, you can have:

contactId Firstname surname address dateofbirth
1 jo bloggs 1 my road 7/8/70
2 jo bloggs 1 my road 7/8/70

The record itself isn't a duplicate because the contactid
is different, but the contact details are. In oracle (and
I think sybase) I could probably add a unique composite
key constraint on firstname + surname + address +
dateofbirth to create a (probable) unique contact record.

What would be the recommended table design in Access?

Many thanks,


Dan.

 

[Jeff Boyce]

Dan

You can create a composite key in Access, too, and make it Unique. However,
that only prevents EXACT duplicates, not possibles, like below:

contactId Firstname surname address dateofbirth
1 jo bloggs 1 my road 7/8/70
2 joe bloggs 1 my road 7/8/70
3 joe bloggs 1 my road N. 8/7/70
4 joe bloggs 99 other road 7/8/70

(all the same, with more than one address, plus typos)

Good luck

Jeff Boyce
<Access MVP>

 

[dan]

Hi Jeff,

(I think) there will be a problem with the composite
primary key solution.

I have lots of tables related to the contacts table via
the current ContactId primary key. In order to create the
composite primary key, I would have to demote contactid
form being the primary key and instate the
firstname,surname,address,dob fields as the compoosite
primary key.

In doing so, I am worried that the table relationships
(which I have set up in the Relationships section) will
break.

Would dropping contactid as the primary key cause this
problem or will i be okay???

Tanks, Jeff.

Dan.

 

[Tim Ferguson]

(I think) there will be a problem with the composite
primary key solution.

I think he meant a composite unique index; i.e. non Primary. Which is what
you suggested in your original post, and yes, Access can do it.

From the table design window, click the little button with the lightning
bolt to show the Indexes design window. This is, I am afraid, not the
tidiest of dialogs:

In the first column, put a name for the index like idxFullNameAddr or
something. In the bottom half, check primary OFF, unique ON, and ignore
nulls to whatever you like. In the second column, choose FirstName;
immediately below that, pick Surname; and then in the third row select
Address. Then save everything.

HTH


All the best


Tim F

 

[Jeff Boyce]

Yes, thank you Tim. I was "incommunicado" and didn't have a chance to
mention that not all keys are primary. And an "index" serves quite nicely,
thank you.

Jeff Boyce
<Access MVP>

 

[dan]

Fantastic, just what the doctor ordered!

Thanks both,

Dan.