Prevent duplication of records in two fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FreeMaster - 12 Dec 2004 06:23 GMT

Hi,
I need help to prevent duplicating records in two combining fields. Below is
an example of my data base and what I'm trying to do.

Field 1 Field 2
A 1
A 2
B 1
B 3
A 1 <<<< duplicate combination, shall be prevented.

any ideas ?

Thanks,
WL

Reply to this Message

'69 Camaro - 12 Dec 2004 06:48 GMT

Hi.

You need to create a unique index on the two fields. And don't allow null
values in either of these fields, unless both fields are "required." Even
then, it's easy to trip on the use of nulls when using SQL.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

> Hi,
> I need help to prevent duplicating records in two combining fields. Below is
[quoted text clipped - 11 lines]
> Thanks,
> WL

Reply to this Message

FreeMaster - 13 Dec 2004 11:23 GMT

I appreciate your reply.

I tried it but didn't work. It prevents me from entering same data in one
field twice. but that's not what I need. It shall prevent repeating combined
values in two fields.

Thank you,
WL

> Hi.
>
[quoted text clipped - 28 lines]
> > Thanks,
> > WL

Reply to this Message

'69 Camaro - 15 Dec 2004 13:44 GMT

Hi.

To create a unique index on two fields, open the table in Design View.
Select the first field of your unique index. Set the "Required" Property to
"Yes." Select the second field of your unique index. Set the "Required"
Property to "Yes."

Right click on the Design View Title bar and select the "Indexes" item on
the pop-up menu to open the "Indexes: MyTableName" dialog window. In the
"Index Name" column, type a name for the unique index in the first empty
row. In the "Field Name" column of that row, select the name of the first
field of your unique index. In the next row of that same column, select
name of the second field of your unique index.

Move the cursor back to the first row of the unique index so that the Index
Properties appears (again). Change the "Unique" Property to "Yes." Close
the "Indexes: MyTableName" dialog window. Save the table.

This unique index on two fields will prevent users from entering duplicates
of the combined fields.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

> I appreciate your reply.
>
[quoted text clipped - 37 lines]
> > > Thanks,
> > > WL

Reply to this Message