stumbling with a relationship
I've been trying to figure out how to make this work. I've looked around and I can't. Here's the scenario.
I have two tables. The first is called "Properties" and details property insurance. The fields of importance in this table are:
- Coverage Limit - a dollar value defining the limit of coverage for the policy
- k_propertyID - key value
Each policy can have a variety of deductions applied to it. These are all defined in another table called "_Property Deductions". This table has the following fields:
- Description - what the user selects to apply the deduction
- Discount - a percentage discount to be applied to the premium
- High Value - works in conjunction with Low Value to determine which deduction is applicable (ie. "Properties::Coverage Limit" must be less than this number)
- Low Value - works with High Value (ie. "Properties::Coverage Limit" must be greater than or equal to this number)
- k_propertyDiscountID - key value
The relationship needs to be many-to-many. I've been trying to figure out how to use a join table to make this work. My stumbling block is making the "Coverage Limit" field assist in determining which discount applies. Ideally I'd like to set up a portal to the join table within which a user can select the "_Property Deductions::Description" they'd like to apply. It's the relationship from the join table to the "_Property Deductions" table that requires a reference back to the parent of the join table to fetch "Properties::Coverage Limit".
I could really use some assistance with this. It's probably just something I'm not seeing. Thanks to anybody who can help me out.