I inherited an Access 2003 database that was not normalized properly and had no relationships defined. That was all ok for the last sesquiyear, but not anymore. I must tread very carefully lest I break up to three everyday applications. The applications will not know or care about adding new tables, so that is the one schema change I can make unfettered.
The WebLogin has a composite key, AccountNo and SubID. The CustomerBase also has a AccountNo + SubID composite key. AccountNo columns always correspond to each other. SubID columns can correspond to each other, but they do not have to.
The new query needed will be to look up records in CustomerBase according to the AccountNo you found in WebLogin. If I select all rows from WebLogin where AccountNo = "012345", they will correspond to all rows in CustomerBase where AccountNo = "012345" regardless of whether or not the SubID columns are equal. In most cases, a single AccountNo + SubID pair will be selected from WebLogin, and I must locate all matching AccountNo's in CustomerBase.
I have already corrected all situations with fuzzy matches, such as "0123456" in CustomerBase vs. "123456" in WebLogin. In all cases, AccountNo columns really are equal now where before they were not.
I drew the AccountNo line from table to table easily enough, but I cannot use the "Enforce referential integrity" checkbox. What should I now do to make the kind of relationship I want?
The WebLogin has a composite key, AccountNo and SubID. The CustomerBase also has a AccountNo + SubID composite key. AccountNo columns always correspond to each other. SubID columns can correspond to each other, but they do not have to.
The new query needed will be to look up records in CustomerBase according to the AccountNo you found in WebLogin. If I select all rows from WebLogin where AccountNo = "012345", they will correspond to all rows in CustomerBase where AccountNo = "012345" regardless of whether or not the SubID columns are equal. In most cases, a single AccountNo + SubID pair will be selected from WebLogin, and I must locate all matching AccountNo's in CustomerBase.
I have already corrected all situations with fuzzy matches, such as "0123456" in CustomerBase vs. "123456" in WebLogin. In all cases, AccountNo columns really are equal now where before they were not.
I drew the AccountNo line from table to table easily enough, but I cannot use the "Enforce referential integrity" checkbox. What should I now do to make the kind of relationship I want?