referential integrity with blank records

[markjkubicki]

2 table: tbmMfr_RepAgency and tbmMfr



tbmMfr_RepAgency includes a required field entry: RepAgency_Name

within tbmMfr are fields: Mfr_Name (required) and Mfr_RepAgency (NOT required, *BUT*, when it does, the entry must have integrity with a record at tbmMfr_RepAgency.RepAgency_Name.


The problem "seems" to be that the tbmMfr records with no entry in Mfr_RepAgency are blocking referential integrity

Is there a work around?



as always, with appreciation in advance,
m.

[Welshgasman]

Put something in the field, something that marks it as Not Assigned.
Should be required really, else as you have found, how are you meant to have referential integrity?

[markjkubicki]

makes sense; thnx