If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Question about exclusive fields
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 |
#2
|
|||
|
|||
Question about exclusive fields
On Aug 7, 5:35*pm, John Harrington wrote:
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 he 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. |
#3
|
|||
|
|||
Question about exclusive fields
On Fri, 7 Aug 2009 15:35:04 -0700 (PDT), John Harrington
wrote: 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. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Question about exclusive fields
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 "Piet Linden" wrote in message ... On Aug 7, 5:35 pm, John Harrington wrote: 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 he 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. |
#5
|
|||
|
|||
Question about exclusive fields
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 "Jeanette Cunningham" wrote in message ... 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 "Piet Linden" wrote in message ... On Aug 7, 5:35 pm, John Harrington wrote: 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 he 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. |
Thread Tools | |
Display Modes | |
|
|