Compound Key Problem

[larpup]

I have a table where the uniqueness is based upon three fields. One
field is numeric (integer) and the other two are text.

I really don't want to make a 3 field compound key for various reasons,
however, I need to ensure that there are no duplicate records in the
table based upon the three fields. (I obviously have a form based upon
this table.)

It's ok to use an autonumber as my key field, however, this will not
accomplish duplications. Can anyone shed any light on how I can
approach this.

Thanks,

Lar

 

[Jeff Boyce]

Lar

Open the table in design mode. Click on the Indexes toolbar button.

In a new row (i.e., under your Primary Key), create a name (first column),
then select the first of your three fields. Without creating another new
name, drop down to the next row and select the second field. Then the
third.

Go back to the first row of this new index (the one where you gave it a
name). Notice that you can set the Unique property to Yes.

This will give you a three-field combination index that enforces unique-ity.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[larpup]

Jeff,

I'm not quite understanding you. I understand how to get to the tool
bar.
What do you me mean "In a new row (i.e., under your Primary Key)? My
AutoNumber?
or.. Do I just create a new field and click Indexes toolbar and then
execute your advice?

Lar

 

[Jeff Boyce]

Lar

Open the table in design mode.

Does the table have a primary key?

Click on the toolbar button that brings up the Indexes window.

Inside that Indexes window you should have a grid with columns labeled
"Index Name", "Field Name", and "Sort Order".

If your table has a primary key, you should have a row in the grid that has
"Primary Key" under "Index Name", and the field name that is your primary
key under the "Field Name" column. Don't worry about the "Sort Order"
column.

In a new (empty) row immediately after the Primary Key row (or the very
first one if all are blank), enter a name for this new three-field index.
If nothing else, you could call it "Alt_Key".

Move over to the "Field Name" column, same row. Using the drop down in that
"cell", pick the first of your three fields. Click in the "Field Name"
column in the next row down and pick the second of your three fields. Click
.... in the next row down and pick the third ...

Now click on the left to highlight the row in which you entered a new index
name ("Alt_Key", if nothing else). The space in the lower portion of the
window should now give several properties, including "Unique". Set that
property to "Yes".

Save the changes, close the design window. The table should now prevent you
from entering "duplicates" on those three fields combined.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[larpup]

Jeff,

I can't thank you enough for this extremely timely advice. I used my
Primary Key, which is an autonumber, and followed your directions. They
work perfectly.

I really appreciate the assitance very much.

Lar