Validation rules question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

UnderSeven - 24 Feb 2007 06:16 GMT

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!

Reply to this Message

Ken Sheridan - 24 Feb 2007 16:15 GMT

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

> Hi,
> I am attempting to do a validation rule on the table level which would
[quoted text clipped - 7 lines]
>
> Thanks!

Reply to this Message

UnderSeven - 24 Feb 2007 20:02 GMT

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.

> 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
[quoted text clipped - 14 lines]
> >
> > Thanks!

Reply to this Message

Douglas J. Steele - 24 Feb 2007 22:46 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> How do I access the SQL of a table? I see what you're saying and I know
> what
[quoted text clipped - 23 lines]
>> >
>> > Thanks!

Reply to this Message

Ken Sheridan - 25 Feb 2007 17:35 GMT

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

> 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
[quoted text clipped - 18 lines]
> > >
> > > Thanks!

Reply to this Message

Jamie Collins - 27 Feb 2007 11:50 GMT

On Feb 25, 5:35 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

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

--

Reply to this Message