Relationship? Referential Integrity?

[Jeff]

What am I missing

2 Tables - "Loan Data" and "Lock Data"

Both tables have Loan Number as a key, but not a primary
key....indexed but no duplicates.

I have created a relationship between the two tables,
linking "Loan Number".

What I am trying to accomplish is allowing a loan number
entry into the Lock Data Table for any loan number, but
if the loan number already exists in the Loan Data Table,
pull the fields from that record into the like fields in
the Lock Data Table, and then also pass back a date from
the Lock Data Table to the Loan Data Table and update the
field for that record in the Loan Data Table.

Hopefully this doesn't require programming..........

 

[John Vinson]

What am I missing

2 Tables - "Loan Data" and "Lock Data"

Both tables have Loan Number as a key, but not a primary
key....indexed but no duplicates.

I have created a relationship between the two tables,
linking "Loan Number". =
What I am trying to accomplish is allowing a loan number
entry into the Lock Data Table for any loan number, but
if the loan number already exists in the Loan Data Table,
pull the fields from that record into the like fields in
the Lock Data Table, and then also pass back a date from
the Lock Data Table to the Loan Data Table and update the
field for that record in the Loan Data Table.

Hopefully this doesn't require programming..........

You're missing the point of how relational databases work.

You should NOT store these fields redundantly in the two tables.
Relational databases use the "Grandmother's Pantry Principle": "a
place - ONE place! - for everything, everything in its place".

If you have loan data, it should be stored in the Loan data table, and
*ONLY* in the loan data table. If you have different types of
information for Locks, that information should be stored in the Loan
Lock table. And a table without a primary key is all but useless,
since you cannot identify which record is which.

You should identify your "Entities" - real-life things, persons, or
events; each type of Entity should have its own table, and each record
of that table should contain information about one unique instance of
that Entity. That is, information that is unique to a Loan should
exist in a single record of a Loan table, with the Loan Number as that
table's Primary Key. If there is other information about a loan
(payments, multiple debtors, etc.) you will need a one to many
relationship to another table in which that information should be
stored, using the Loan Number as the foreign key to link the two
tables; the second table should NOT contain any Loan Data fields other
than the loan number itself.