Here's my challenge.

I work for a travel agency and we are creating a system for looking up the lowest cruise fare pricing. The pricing table I have created has these fields:

SailDateID, Category, Price

(I'm oversimplifying, but these fields are involved in my challenge)

What I need to do is identify the lowest price per saildate per category.

What I've done that works is this:

I created a calculation field called "SailDateIDCat" as follows:

SailDateID & "-" & "Category" which returns, for example, "34-AA", "34-AB", etc.

(It's likely that more than one record will exist for each category as multiple promotions can apply to each category.)

Anyway, I created a self-join relationship using this calculation field, then created another calculation called "LowestPrice" as follows:

"Min(SJ via SailDateIDCategory::Rate)"

Then I created a third calculation called "TagLowest" as follows:

If(Rate = Lowest Price, SailDateID, "")

This results in a "Y" in only the lowest rate for the category's records. So what's the problem?

I cannot create another Self Join relationship to TagLowest because it cannot be indexed (because Lowest Price references a related field and Lowest price is referenced in TagLowest) and if I do a script which finds all records with the SailDateID in TagLowest, it takes *FOREVER* because it cannot index the field (for the same reason it can't Self-Join) so it must search through all the records. There are currently 820 records, but this will grow to over 50,000 so if it's slow now, it will be unbearable later.

A friend has demonstrated that Access can pull this off using queries (we exported my pricing data to Access and he whipped it out in about 10 minutes). Am I stuck going to Access?

This is important. If I must go to Access, I will only put this pricing info there and try to get to it using FMPro's ODBC/SQL. Can an Access file on a PC be used as a data source to a Mac Server running FM Pro 5 Server?

If anyone helps me with this, I'll buy beers!

Sean

Why not display all the self join records in a portal based upon SailDateID-Category (make sure this is ASCII indexed!) sorted by increasing order of price. It won't "find" the lowest, but if you make the portal one row, the effect will be the same. -bd

Sean,

Did you solve this problem? It would seem to me that the solution offered would still take a long time, since sorting quickly requires an indexed field.

If you would like, send me a skeleton file and tell me exactly what you need. I love a good challenge, but I want to make sure that I'm clear on what you need.

Chuck

[email protected]