Hi.
Is it possible to create multiple field unique index on the table.I want to
have two fields that are uniquely
identifying a record in the table.
Yes. This can be a primary key or a candidate key. In your case this
unique index should be a candidate key and a single field surrogate key
should be used for the bound field in the combo box lookup.
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.
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.
I also need to make an
AutoLookup query which require a unique index but in my
aplication that index must be based on two fields.
The AutoLookup query must use only one field, not two, because the combo box
can only be bound to one field. Use an Autonumber surrogate key to bind the
combo box to. The table has the unique index on the two fields to prevent
duplicates, so the Autonumber surrogate key works well as the primary key for
the table.
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.)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. (Only "Answers" have green
check-marks.) Remember that the best answers are often given to those who
have a history of rewarding the contributors who have taken the time to
answer questions correctly.
