Prevent duplication of records in two fields

[FreeMaster]

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

 

['69 Camaro]

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.)

 

[FreeMaster]

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

 

['69 Camaro]

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.)