Conditionally Required Columns

[Andrew]

If table1 has the columns c1, c2, c3 and c4, is there any way to make c1
required, c2 optional, and one of either c3 or c4 required? For example,

c1 c2 c3 c4
x x x =valid
x x x =valid
x x =invalid since either c3 or c4 must be present
x x x x =invalid since both c3 and c4 can't be present

Also, one more constraint just to make things interesting: the solution must
be compatible with Access 2000.

Thanks for any help with this problem.

 

[Jerry Whittle]

Not at table level. You might be able to write some code to check in a form.

However if the entry in one field is dependent of entries of other fields,
there's a very good chance that your data isn't normalized properly. Is so,
this will be just the first of many problems from this design. You might want
to step back and rethink.

 

[Larry Daugherty]

If a table has a column then the table has the column. You can choose
which columns to include in queries or to display at various times but
you don't normally change your schema dynamically.

HTH

 

[Andrew]

Thanks for the quick and helpful response Jerry. The database transforms the
data it contains into XML and the XML Schema uses several Choice operators.
I know how this could be implemented using forms, but I was trying to
implement these choices in the tables where the users will be inserting data.
Unfortunately it doesn't seem as if that is possible, but thanks again for
you help.

 

[John W. Vinson]

I was trying to
implement these choices in the tables where the users will be inserting data.

That's your problem, then. Tables are NOT designed for data entry or data
interaction - they're much too inflexible!

It's really best to keep tables "under the hood" and provide Forms for your
users' interaction with the data.

That said... you can create a table validation rule in the Table's properties
such as

([C3] IS NOT NULL AND [C4] IS NULL) OR ([C3] IS NULL AND [C4] IS NOT NULL)

with an appropriate validation text property, such as "Fill in only one of C3
or C4, not both".

John W. Vinson [MVP]