Q: On combining two columns

[Mary]

Hi,

I have tableA which includes columnA (int) with a product code: 1,2,3 etc.
(1=red, 2=blue, etc.).
Then I have columnB (int) with a product number, 1000, 1001, etc.
The combination of code and type cannot be duplicated,
ex.:
1/1000, 1/1001, 1/1002 is ok.
2/1000, 2/2001, 2/2002 is ok
but
1/1000 and 1/1000 or 2/1001 and 2/1001 is not ok since the product number is
being duplicated having the same code.

Q: How do I go from preventing the duplication of the relation of
code/product?
I would appreciate some assistance.
Mary

 

[Andrew Smith]

You add "columnA" and "columnB" to the same index and make the index unique.
This could be the primary key, but it does not have to be.

This comes from the Access help topic "Prevent Entry of Duplicate Values":

1.. Create a multiple-field index using the fields you want to prohibit
duplicate values for. Leave the Indexes window open when you have finished
defining the index.
How?

1.. Open the table in Design view.
2.. Click Indexes on the toolbar.
3.. In the first blank row in the Index Name column, type a name for the
index. You can name the index after one of the index fields, or use another
name.
4.. In the Field Name column, click the arrow and select the first field
for the index.
5.. In the next row in the Field Name column, select the second field
for the index. (Leave the Index Name column blank in that row.) Repeat this
step until you have selected all the fields you want to include in this
index.
Note The default sort order is Ascending. Select Descending in the Sort
Order column of the Indexes window to sort the corresponding field's data in
descending order.

2.. In the upper portion of the Indexes window, click the new index name.
3.. In the lower portion of the Indexes window, click the Unique property
box, and then click Yes.