AnsweredAssumed Answered

stumbling with a relationship

Question asked by BrianSwitzer on Mar 31, 2011
Latest reply on Apr 5, 2011 by BrianSwitzer


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:

  1. Coverage Limit - a dollar value defining the limit of coverage for the policy
  2. 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:

  1. Description - what the user selects to apply the deduction
  2. Discount - a percentage discount to be applied to the premium
  3. High Value - works in conjunction with Low Value to determine which deduction is applicable (ie. "Properties::Coverage Limit" must be less than this number)
  4. Low Value - works with High Value (ie. "Properties::Coverage Limit" must be greater than or equal to this number)
  5. 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.