Relation to an interval of numbers?

[Lange]

I have two databases, where I am trying to relate records by a number. The problem is, that in the first database I do not have the exact number, but an interval (given by values in two fields).

Example: Database one, record A: Interval 1-24 (field A=1, field B=24).

Database two, record B: Value=17

- how do I relate these records?

In plain words, I need database two to look up values if it itself has a value in an interval specified in database one.

The problem is my intervals can be as big as 1700, else I could populate a field with the values in between the interval.

[Chuck]

This is fairly straight forward in FM7.

Create the relationship by dragging from one table to another using the field A and value fields (in database one and two). After the relationship has been created so that records are linked if these two fields are equal, double click the equal sign that connects the two tables. Edit the existing relationship so that instead of equals it uses the <= symbol (so that field A <= Value). Use the popup menu that appears between the field lists.

Then in the field list for database one, choose field B, select >= from the popup menu, and click Add. Now the relationship will be based on two criteria: field A <= Value and field B >= Value.

Doing a similar thing in FM6 or below is much more complex. Let me know if you need instructions.

Chuck

[Lange]

Thanks Chuck! I see this would be an argument for upgrading to FMP 7...

But for the moment I have to stick with vers. 6 so any ideas are greatly appreciated.

[Ender]

Try Mikhail Edoshin's Smart Ranges technique. It's a bit complex, but you can disect the examples in his First Edition page:

http://www.onegasoft.com/tools/smartranges/