Relationship help needed...Before I go mad!

[moonunit]

I'm trying to set-up a mileages db and I figured that this might be best accomplished with a Portal...

The objective is to automate mileage entry in a trip-itinerary

Table 1 - Mileages

Field 1 - city1 [text] // from

Field 2 - city2 [text] // to

Field 3 - miles [number]

Field 3 - city_city [calc text]

(city1 & " to " & city2 & " " miles & " miles")

Table 2 - Itinerary

Field 1 - city [text] //insert city for match

Portal - city_city

The relationship is made between Field 1 = Field 1

This works...But I can't get to the next step...

By way of example...

San Francisco - LA

San Francisco - Seattle

LA - Seattle

In this example the portal shows both SF mileages but if you look for a match based on LA you only get LA-Seattle and if you search for Seattle nothing appears.

So the next relationship step ought to be Field 1 = Field 1 and Field 1 = Field 2

I've spent hours toying with this and I'm now officially snow-blind!

Offers of directional (!) guidance would be much appreciated.

Tim

[comment]

Try creating a text calculation in the Mileages table =

City1 & ¶ & City2

and match the city in the Itinerary tabe to this field.

Edited November 7, 2005 by Guest
Misunderstood the problem

[moonunit]

Thank you!

Such a simple solution - I would never have thought of that, which is worrying!

I've now expanded this enormously and having spent all day on it, I can guarantee that the next problem is fast approaching!

Tim