Need a tricky Calc

[stuj1026]

Hi all Need some help here

In a field (Florida Rates)I have this information

Dade 1.95

Broward 1.84

Palm Beach 1.840

Martin & St. Lucie 1.3

Rest of State .875

North East 1.150

I have another field called search..What I need is a calc that when I enter (as an example) into the search field "Rest of State" the field will return .875

or if I enter into search, "North East" the calc will return 1.150

Any help will be greatly appreciated

Thanks Stu

[comment]

Try it this way: create a table of Rates, with (at least) 2 fields, Location (text) and Rate (number). Define a relationship based on Location between your existing table and the new one, and make your rate field a lookup.

[stuj1026]

That really wont work for me . I would have to set up another table which means that for each of the 1500 records i would have to have 15 records as I have 15 rates for each file in the main database as each rate is different. that would mean 22,500 record in the new table.. The Calc would be much easier to implement.

I originally had

If ( PatternCount ( Text; Search);LeftWords ( Right ( Text; Length (Text )-Length ( Search )-Position ( Text; Search; 1; 1)+1); 1); 0)

but if the number following the search was .87 the calc returns 87 . need a modification that returns .87 .

Thanks for the input

Stu

[comment]

It's rather difficult to follow your logic, given the little information you have provided. In your first example, there are only 6 rates - so that would be 6 records in the Rates table, serving ANY number of records in whatever your current table is.

If indeed you do have 22,500 different RATES, then they have to be kept somewhere anyhow. You can lump them together in a giant text field, which will require a complex calc to get them out, or you can put them where they can be easily accessed. It's a matter of putting your needles in a haystack vs. putting them into labeled drawers.

[stuj1026]

Thanks for all your time with this.. Let me be more specific I have 1500 specialties. Each specialty can have up to 15 rates..

___________________

Record #1

Field = Specialty General Practice

Field = rates contains

Dade 1.95

Broward 1.84

Palm Beach 1.840

Martin & St. Lucie 1.3

Rest of State .875

North East 1.150

_________________________

Record#2

Spinal Surgery

Dade 2.95

Broward 2.84

Palm Beach 1.840

Martin & St. Lucie 6.3

Rest of State .875

North East 2.150

Etc..

All this info is already in the database as such

so rather than create a new table and rekey everything i was hoping for the calculation that would return the data needed

Thanks again for all your time

Stu

[comment]

There's no need to rekey manually - a script can create the separate records for you. The point is (if I understand correctly) that you do have some 20,000 distinct rates stored in your database. So the question is not how many, but what's the best way to organize them. The fact that a "tricky calc" is required to get the correct rate should serve as a hint.