Unanswered: Linking help on a many to many relationship
Structure of the Data Base
The table we were discussing are as follows
tblMortgage, tblMortgageSatisfaction, and tblSatisfaction
Each mortgage can be satisfied multiple times
Each satisfaction can satisfy multiple mortgages
The DB is designed that when working with the second mortgage, you’re able to link it to the satisfaction from the previous mortgage.
Each mortgage or satisfaction can have a correction record. The DB is designed to save the correction into the same table as the original record.
In other words, a correction is not saved in a subtable and is not linked to the original record. However, the satisfaction and the correction are both linked to the mortgage.
Explanation of the fields on the satisfaction table
SatisfactionRecordID is an auto number field
SatisfactionID will be the same for the satisfaction and all its corrections
Correction number will start with one and will increase with each correction
Here comes the problem
When I’m working with the second mortgage and link it to the satisfaction from the previous mortgage, I want all the satisfaction corrections should also be linked to the same mortgage. (The only place this action will have to take place is the probably tblMortgageSatisfaction)
How can I do this?
Your assistance is greatly appreciated.
For your convenience I have attached a sample DB
I assume that a solution would be to grab the SatisfactionID from the record I’m linking, (Not the SatisfactionRecordID) and loop the tblSatisfaction and link all records that has that satisfaction ID (including corrections)
The second option could be to save all corrections into a Subtable instead of the same table.
(The same question applies to the Cema Records.)
If option two makes more sensible, is it worth to redesign the entire database?
However before I make any change, I would like to have your opinion on this.