Relationships based on inequalities?

[Charlie]

All -

Just curious if anyone has a nifty trick or other method for relating records based on inequalities.

For example, if I wanted to relate record with field Date of value 10/15/01 to all other records that have a Date value of 10/15/01 or less.

I've toyed with trying to have a secondary calc field that gets all Date values for records, but the concept becomes circular. I still need a way of isolating the records that are "less than" the record in question.

I have certainly found solutions to this, but they always involve isolating records and using a running-total summary field. I was just wondering if anyone had some magic bullet that would allow a relationship-only solution.

Many thanks.

Charlie

[LiveOak]

Date range relationships are accomplished using mulit-keys. If you want a record to match a range of dates, build a text key with multiple values separated by carriage returns. This can be built using a looping script or for large keys, using a plug-in designed for this purpose. For example, a key for the dates 10/1/2001...10/5/2001 would look like:

10/1/2001

10/2/2001

10/3/2001

10/4/2001

10/5/2001

This type of key can be used on either the right or left side of a relationship or both.

-bd

[BobWeaver]

The other option is to use a lookup instead of (or in combination with) a directly related field. If you use the "use next lower value" option, it will pull in the value you want.

[LeCates]

Hi Charlie,

If you knew the date value against which you want to link ahead of time, or it changed rarely, you could pre-calculate your results in the target file and then match from a constant.

If you don't have the above luxury, there is a method (complex) for indexing the date range from Date 0 to Date N, which is the same as Date range less than Date N. Given Date N is a field called d, here is the calc:

code:

---

=

Substitute(Middle(" 0 1 2 3 4 5 6 7 8 9", 1, (Int((d-1)/10^0) + 1) * 2), " ","