I have never been good at relationships (sounds like a bad first date opening line)
two (2) tables:
tblManufacturers
tblManufacturers_RepAgencies
both have a field: RepAgency (both: short text, 255 char )
a RepAgency can have many Manufacturers
a 'Manufacturer' can only have (1) RepAgency -OR- may not have a RepAgency
trying to enforce referential integrity between the 2 tables so that if a RepAgency changes its name (they usually do not, but once in a while ...they do), or dissolves (that happens all of the time), the change cascades into tblManufactcurers
creating a queries:
Find duplicates for tblManufacturerRepAgencies yields none
Manufacturers Without Matching tblManufacturerRepAgencies yields only Manufacturers with no RepAgency (which is a valid entry)
in tblManufacturers_RepAgencies, the field: RepAgency is a required index,
consequently, there can be no records with a null RepAgency (to match Manufacturers with no agency)
and a possible work-around (if one is needed to address this condition)
... RepAgency: "-none-"
really isn't something we want to do (although ...reluctantly... we could)
any thoughts will be appreciated with thanks in advance
m.