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

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

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.

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), " ","