Microsoft Access 2007
No forms are involved.
I am trying to enforce referential integrity between two tables, but only if there is a non-null value in one of the foreign fields of a compound index. I am not having any success.
Tables 1 and 2 are related such that each record in table 2 is related to some record in table 1. Table 3 is the table with the issue. All records in table 3 must be related to a record in table 1 but need not be related to a record in table 2 unless the opCode field is Not Null. The problem seems to be that unless all of fields in the foreign key for table 3 are Null then they are collectively evaluated against table 2.
Is there any way around this?
Table 1
locNum (Primary Key)
locCode (Primary Key)
locDesc
Table 2
locNum (Primary Key)
locCode (Primary Key)
subLocCode (Primary Key)
subLocDesc
Table 3
recId (Primary Key)
locNum (Required)
locCode (Required)
subLocCode (Not Required, no default value)
recDesc
thanks,
Paul
No forms are involved.
I am trying to enforce referential integrity between two tables, but only if there is a non-null value in one of the foreign fields of a compound index. I am not having any success.
Tables 1 and 2 are related such that each record in table 2 is related to some record in table 1. Table 3 is the table with the issue. All records in table 3 must be related to a record in table 1 but need not be related to a record in table 2 unless the opCode field is Not Null. The problem seems to be that unless all of fields in the foreign key for table 3 are Null then they are collectively evaluated against table 2.
Is there any way around this?
Table 1
locNum (Primary Key)
locCode (Primary Key)
locDesc
Table 2
locNum (Primary Key)
locCode (Primary Key)
subLocCode (Primary Key)
subLocDesc
Table 3
recId (Primary Key)
locNum (Required)
locCode (Required)
subLocCode (Not Required, no default value)
recDesc
thanks,
Paul