Validation rules question

[Guest]

Hi,
I am attempting to do a validation rule on the table level which would
prevent a duplicate value where a duplicate value exists in a different
column.

So for instance column X can contact the data 1 1 2 3 IF the corresponding
column Y does not contain say A A B C But if either column did not have
duplicate values in the same rows it would be find. So 1 1 2 3 for X and A B
A A For Y would be fine.

Thanks!

 

[Guest]

Create a unique index on the X and Y columns. Any attempt to insert a row
with the same combination of values in X and Y as already exist in another
row would violate the index and raise an error.

Ken Sheridan
Stafford, England

 

[Guest]

How do I access the SQL of a table? I see what you're saying and I know what
SQL code I need to write, but I do not know how to use the Acess GUI to
either access the SQL, or do such an index myself.

 

[Douglas J. Steele]

There isn't "SQL of a table".

Easiest approach would be to open the table in Design view, then choose View
| Indexes from the menu bar. In that dialog, pick a name (any name) for the
index, and put column X beside it as the Field Name. On the next row, leave
the index name blank, and put Column Y as the Field Name. Set the indexes
Unique property in the bottom left-hand corner.

If you'd rather work with the DDL, simply create a new query, but don't
select any tables. Select View | SQL View from the menu. Type your DDL into
that window, and run it.

 

[Guest]

If you do want to use DDL rather than do it via the View menu then the
statement would go something like this:

CREATE UNIQUE INDEX MyIndex
ON MyTable(X, Y);

As Douglas said, just put in it a new query in SQL view and run it. You
don't need to save the query.

Ken Sheridan
Stafford, England

 

[Jamie Collins]

If you do want to use DDL rather than do it via the View menu then the
statement would go something like this:

CREATE UNIQUE INDEX MyIndex
ON MyTable(X, Y);

Alternatively:

ALTER TABLE MyTable ADD
CONSTRAINT MyConstraint
UNIQUE (X, Y)
;

The idea is to maintain a distinction between constraints and indexes:
constraints form part of the schema and are static, whereas indexes
are for performance and can be fine tuned. The fact a UNIQUE
constraint in Jet is implemented via an index is irrelevant because I
do not want the physical implementation exposed in my DDL schema
script.

Jamie.

--