Table validation rule - allow only one occurence in a field

jaworski_m · External Since: Oct 23, 2009 Posts: 2

Hello,
Suppose there's a table (many side):

ID (PK)
ID_foreign (FK)
field 1
field 2
yes/no field

How can I allow ONLY ONE record to have "yes/no field" = True? (with table
validation rule)
In case a user sets a second record "yes/no field" = True an error is
triggered.

Thank you for suggestions.

Access 2003

PieterLinden via AccessMo · External Since: Jun 25, 2009 Posts: 4

jaworski_m wrote:
>Hello,
>Suppose there's a table (many side):
>
>ID (PK)
>ID_foreign (FK)
>field 1
>field 2
>yes/no field
>
>How can I allow ONLY ONE record to have "yes/no field" = True? (with table
>validation rule)
>In case a user sets a second record "yes/no field" = True an error is
>triggered.
>
What happens when you add a unique index (no duplicates) to the yes/no field?
>Thank you for suggestions.
>
>Access 2003

--
Message posted via http://www.accessmonster.com

PieterLinden via AccessMo · External Since: Jun 25, 2009 Posts: 4

jaworski_m wrote:
>Hello,
>Suppose there's a table (many side):
>
>ID (PK)
>ID_foreign (FK)
>field 1
>field 2
>yes/no field
>
>How can I allow ONLY ONE record to have "yes/no field" = True? (with table
>validation rule)
>In case a user sets a second record "yes/no field" = True an error is
>triggered.
>
What happens when you add a unique index (no duplicates) to the yes/no field?
You do realize that with this rule enforced at table level, you can only have
two records in your table, right? (one Yes and one No).
>Thank you for suggestions.
>
>Access 2003

--
Message posted via http://www.accessmonster.com

jaworski_m · External Since: Oct 23, 2009 Posts: 2

> You do realize that with this rule enforced at table level, you can only have
> two records in your table, right? (one Yes and one No).

Yes, correct. But I would like to have Many records (not only 2) and only
ONE with value "Yes".

"PieterLinden via AccessMonster.com" wrote:

> jaworski_m wrote:
> >Hello,
> >Suppose there's a table (many side):
> >
> >ID (PK)
> >ID_foreign (FK)
> >field 1
> >field 2
> >yes/no field
> >
> >How can I allow ONLY ONE record to have "yes/no field" = True? (with table
> >validation rule)
> >In case a user sets a second record "yes/no field" = True an error is
> >triggered.
> >
> What happens when you add a unique index (no duplicates) to the yes/no field?
> You do realize that with this rule enforced at table level, you can only have
> two records in your table, right? (one Yes and one No).
> >Thank you for suggestions.
> >
> >Access 2003
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>

KARL DEWEY · External Since: Mar 03, 2006 Posts: 1616

I do not hink you can do it in a table but in a form.

If DSum <>0 then message and not allow change of checkbox.

--
Build a little, test a little.

"jaworski_m" wrote:

> > You do realize that with this rule enforced at table level, you can only have
> > two records in your table, right? (one Yes and one No).
>
> Yes, correct. But I would like to have Many records (not only 2) and only
> ONE with value "Yes".
>
>
>
> "PieterLinden via AccessMonster.com" wrote:
>
> > jaworski_m wrote:
> > >Hello,
> > >Suppose there's a table (many side):
> > >
> > >ID (PK)
> > >ID_foreign (FK)
> > >field 1
> > >field 2
> > >yes/no field
> > >
> > >How can I allow ONLY ONE record to have "yes/no field" = True? (with table
> > >validation rule)
> > >In case a user sets a second record "yes/no field" = True an error is
> > >triggered.
> > >
> > What happens when you add a unique index (no duplicates) to the yes/no field?
> > You do realize that with this rule enforced at table level, you can only have
> > two records in your table, right? (one Yes and one No).
> > >Thank you for suggestions.
> > >
> > >Access 2003
> >
> > --
> > Message posted via http://www.accessmonster.com
> >
> > .
> >