Complex Relationships w/ Multiple Predicates

[RK]

Headache...Any help would be very much appreciated.

I am trying to look-up information based on a "Complex Relationship" that specifies a date range.

Table A — Has event records with a declared date.

Table B — Has contract records with reference numbers and rates. Each contract record has an "effective date" and a "termination date".

My relationship is defined:

Table B: Customer ID = Table A: Customer ID

And

Table B: effective date ≤ declared date

And

Table B: termination date ≥ declared date

Problem: the "Look-Up" fields I've defined in Table A do not bring in valid data in Table B.

What's broken?

Also...

Any other way I can manage this general task ?

Thanks in advance

Edited July 14, 2009 by Guest