Question about exclusive fields

[John Harrington]

Suppose I have a table where I want two fields to be exclusive, such
that when one field has a specified value the other in the same record
cannot take a value and vice versa.

For example, imagine a student db where students are either graduate
or undergraduate level students. One field relates to a table of
graduate programs. The other field relates to a table of
undergraduate programs. A student can take one path or the other, but
not both simultaneously.

Is my desire to do this sort of exclusion a symptom of bad table
design, and, if so, what should I do besides?

If not, how do I make the fields exclusive, preferably in the design
of the table itself?


Thanks,
John

 

[Piet Linden]

Suppose I have a table where I want two fields to be exclusive, such
that when one field has a specified value the other in the same record
cannot take a value and vice versa.

For example, imagine a student db where students are either graduate
or undergraduate level students.  One field relates to a table of
graduate programs.  The other field relates to a table of
undergraduate programs.  A student can take one path or the other, but
not both simultaneously.

Is my desire to do this sort of exclusion a symptom of bad table
design, and, if so, what should I do besides?

If not, how do I make the fields exclusive, preferably in the design
of the table itself?

Thanks,
John

No, it's not a symptom of bad table design at all. Sometimes you have
mutually exclusive options - it's just that you are modeling something
complex. There's an example of modeling a scenario like this on
Access Web, right here: http://www.mvps.org/access/tables/tbl0013.htm

There's a sample database to download, so you can see the source code
that makes it work.

 

[John W. Vinson]

Suppose I have a table where I want two fields to be exclusive, such
that when one field has a specified value the other in the same record
cannot take a value and vice versa.

Such fields violate the basic tenets of database normalization: a field should
depend ONLY on the table's primary key, and on no other field.

For example, imagine a student db where students are either graduate
or undergraduate level students. One field relates to a table of
graduate programs. The other field relates to a table of
undergraduate programs. A student can take one path or the other, but
not both simultaneously.

Is my desire to do this sort of exclusion a symptom of bad table
design, and, if so, what should I do besides?

It is; I would suggest that you have just ONE field recording which path they
took. Or am I misunderstanding? What value would be stored in these two
fields?

If not, how do I make the fields exclusive, preferably in the design
of the table itself?

You *could* use a Table Validation Rule to require that one field be NULL if
the other is not NULL, but it would still be incorrect design.

 

[Jeanette Cunningham]

There is another solution to this which uses table validation rules and the
XOR operator.
Have a look at validation rules for tables (skip the validation rules for
fields and scroll further down). Here is the link

http://allenbrowne.com/ValidationRule.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Suppose I have a table where I want two fields to be exclusive, such
that when one field has a specified value the other in the same record
cannot take a value and vice versa.

For example, imagine a student db where students are either graduate
or undergraduate level students. One field relates to a table of
graduate programs. The other field relates to a table of
undergraduate programs. A student can take one path or the other, but
not both simultaneously.

Is my desire to do this sort of exclusion a symptom of bad table
design, and, if so, what should I do besides?

If not, how do I make the fields exclusive, preferably in the design
of the table itself?

Thanks,
John

No, it's not a symptom of bad table design at all. Sometimes you have
mutually exclusive options - it's just that you are modeling something
complex. There's an example of modeling a scenario like this on
Access Web, right here: http://www.mvps.org/access/tables/tbl0013.htm

There's a sample database to download, so you can see the source code
that makes it work.

 

[Jeanette Cunningham]

Having posted this, I agree with John Vinson that there is a better design
where you have the student type in one field and the course in another
field.

You control the courses displayed on the form by looking at the student
type. If a graduate, only allow graduate courses to be entered and vice
versa.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia