I am trying to make an accounting database that includes 3 (problematic) tables.1)
tblAccounts
keyAccountID
AccountName
OpeningBalance...
2)
tblLoanAccountsSupplemental
AccountID (key)
LoanName
InterestRate...
3)
tblRealEstateAccountsSupplemental
AccountID (key)
PropertyName
Address...
The idea is that ALL ACCOUNTS have the information in table 1, and specific SETS of accounts have the ADDITIONAL information in each supplemental table (at the same group level). A one-to-many relationship would not be correct here, and I've tried to use a 1-to-1 relationship.
PROBLEM: When I make a new item in tblAccounts or (for example) tblLoanAccountsSupplemental, I want it to autocreate the KEY and make it the same for both tables.
My intuition may be wrong with the 1-to-1 relationship. What should I do?
