Validation rule to allow only one True

[Guest]

I'm trying to create a table to hold rep system id's for my company. Reps
can have multiple id's on multiple systems. I have the table set-up so that
each ssn/repid/system combination is unique. But there is a forth field to
identify which repid is the primary id for that rep on that system. How can
I create a validation that say for each ssn/system combination there can only
be one primary (where primary is a BIT field.)

 

[Ken Snell [MVP]]

You won't be able to do it at the table level. You'll need to enforce it in
your forms that allow people to enter/edit data. Use programming in the form
to validate that only one representative has been checked as primary for a
specific combination, and then don't allow another one to be checked unless
no one is checked.

 

[david epsom dot com dot au]

Make it a numeric field (byte), allow Nulls
Validation rule IS NULL or -1
NO DEFAULT VALUE!
Unique index on the numeric field with the ssn/system

Most of the records will have a Null value in the field.
(in Access/Jet, a unique index allows multiple nulls).
Only one record for each ssn/system will be allowed to have
a value of True (-1).

This means that your two types of record will be Null and
True, rather than False and True, which may require a little
more work on the forms (or you may just get used to it).

(david)

 

[Allen Browne]

Might have to be Integer rather than Byte to get -1?

 

[Ken Snell [MVP]]

Good idea, David.

--

Ken Snell
<MS ACCESS MVP>

Make it a numeric field (byte), allow Nulls
Validation rule IS NULL or -1
NO DEFAULT VALUE!
Unique index on the numeric field with the ssn/system

Most of the records will have a Null value in the field.
(in Access/Jet, a unique index allows multiple nulls).
Only one record for each ssn/system will be allowed to have
a value of True (-1).

This means that your two types of record will be Null and
True, rather than False and True, which may require a little
more work on the forms (or you may just get used to it).

(david)

 

[david epsom dot com dot au]

Might have to be Integer rather than Byte to get -1?

Correct :~)

Thanx

(david)