Adding relationships to a database (1 Viewer)

[Hotaru]

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?

 

[neileg]

You appear to have a many to many relationship between the tables based on account number. You can't, therefore, enforce referential integrity.

You wouldn't normally define a relationship based on values that aren't primary key/ foreign key matches.

It's not at all clear what you want to do.

 

[gemma-the-husky]

note that adding relationships CAN NEVER cause a problem (I dont think)

if you try to add a relationship for which the data already violates the relationship, access wont let you.

if it does let you, it just future-proofs you againt violations in future.

----
The only proviso is that your code will need to react properly if an action fails BECAUSE the relationship constraint has been violated.

 

[WIS]

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?

If you try to enforce RI after the tbls have been inputting data for a while, it usually comes down to "orphaned" records on the "many" side.

What was the actual message you got trying to enforce RI.