Matching field names to field content

[blissland]

I'm not sure whether this is under 'calculations' or 'finding' but I think this is right.

I've got a table of SoilSamples, with fields like 'Season', 'Climate', 'BacteriaLevel' and 'FungiLevel'. I've got another table 'Ranges' that also has 'Season', 'Climate', as well as 'Test', 'low' and 'high'.

The idea is that for each soil sample record, I identify whether the bacteria level and the fungi level is low, normal, or high. So, I've related the two tables thru Season and Climate, and I've set up Case calculations that compare the low and high values with the recorded value to spit out a text answer into BacteriaLevel and FungiLevel.

The problem is that the Test field in the Ranges table has values like "Bacteria" or "Fungi" to identify what kind of test the low and high values relate to. I'm matching field content in one table to field name of another. So how do I make the Bacteria and Fungi fields in the SoilSample table only look at those Range records with the relevant Test?

Below is an example of what the Range records look like:

Season Climate Test Low High

Spring Desert Bacteria 3 7

Fall Tropical Fungi 5 10

Winter Desert Ecoli 2 5

Fall Tropical Bacteria 5 8

How could the Bacteria field be made to look at the low and high values of record 4 and not record 2?

[comment]

This doesn't seem like a good arrangement. Either set up your Ranges table like:

• Season

• Climate

• Bacteria Low

• Bacteria High

• Fungi Low

• Fungi High

• Ecoli Low

• Ecoli High

• ...

or break up your sample data into individual records in a related table:

• DataID

• SampleID

• Test

• Value

• cSeason

• cClimate

The last two fields would be unstored calculations, used together with 'Test' to establish the relationship to the correct record in Ranges.

Otherwise you would need a 'constant' relationship for each possible value of 'Test".

[blissland]

I see how your suggestions here are way better. There is an unfortunate need to stay 'backwards compatible' with this older structure. So I am wondering if there is some way to use Case or something else to accomplish this. Is there some way to tell it to not perform the calculation unless the Test fields contains 'Bacteria'. I tried saying Case(Test ="Bacteria";"the result i want";"the irrelevant result") but I think this only goes to the first record and stops.

[comment]

I tried saying Case(Test ="Bacteria";"the result i want";"the irrelevant result")

And what would be "the result I want"? The problem here is not calculation, but the relationship. The way you have it now, there are several related records, and it's difficult to tell them apart in order to pick the correct one.

[blissland]

Right, the relationship is with "Climate" and "SampleType" and it can't distinguish between the Bacteria, Fungi, and Ecoli records among those related records.

The current calculation for "BacteriaResult" in the Sample table asks whether the value Bacteria1 in that table is lower than the "Low" value or higher than the "High" value. Low and High are fields in TestRanges, along with Climate and SampleType. So Bacteria1 is a number, while BacteriaResult is text "low, normal, or high".

The same will be true of Fungi1 and FungiResult, et al.

I know I could follow your second suggestion of dividing TestRanges up into sections, but that's so unelegant. I think you hinted at using global values but I don't see how that would work when there are so many xResults to determine on the same Table. I was hoping there was something within calculations that would tell it to keep searching until the Test field matched.

[comment]

Right, the relationship is with "Climate" and "SampleType"

IIUC, the relationship is based on Climate and Season - you don't have a Type field in the Samples table, which is the crux of the problem.

The current calculation for "BacteriaResult" in the Sample table asks whether the value Bacteria1 in that table is lower than the "Low" value or higher than the "High" value.

I am afraid you are still missing the point (or I misunderstood your original description). You cannot ask if a value is lower than THE Low value, because you may have three Low values (Bacteria, Fungi, Ecoli) and no way to tell which one of them is Bacteria.

I know I could follow your second suggestion of dividing TestRanges up into sections, but that's so unelegant.

I disagree.

I think you hinted at using global values

No, I haven't.

[blissland]

>>IIUC, the relationship is based on Climate and Season - you don't have a Type field in the Samples table, which is the crux of the problem.

I do. Its not the problem. I think i didn't mention it once trying to keep it simpler.

>>I am afraid you are still missing the point (or I misunderstood your original description). You cannot ask if a value is lower than THE Low value, because you may have three Low values (Bacteria, Fungi, Ecoli) and no way to tell which one of them is Bacteria.

Yes, I am EXTREMELY aware of this. It is the BASIS of my question. Is there ANY way I can do this. I inherited this data structure from how it was being used in an Access db, and somehow that developer made it work in Access. So evidently its somehow possible, even its clearly not the best way to do it.

Dividing things into lots of smaller table is unelegant cuz i'd have to divide it into around 14 tables. my relationships graph is messy enough. ;)

[comment]

Perhaps you should start from the beginning. I don't understand how you can have a Type field in the Samples table, AND at the same time, have fields for BacteriaLevel and FungiLevel (and Ecoli level, I suppose).

Dividing things into lots of smaller table is unelegant cuz i'd have to divide it into around 14 tables.

No one suggested that. The suggestion was (and still is, unless your next explanation turns things around) to have a table of Sample and ONE table for SampleData.

[blissland]

I think my attempt to provide a simpler explanation is muddying things.

The first suggestion that was provided to me in this discussion makes the most sense from a design perspective. but it does require me to manually enter 1000 data points and stray from the company's current way of organizing this info, flawed as it may be. Were it not for the straying issue, i'd implement this immediately and have no further questions.

the second suggestion, I thought, was to divide that TestRanges table with the Low and High values into separate tables for each Test so as to avoid this current 'multiple related records' problem. perhaps i misunderstood what that suggestion was.

Type has nothing to do with Fungi/Bacteria/Ecoli. Its another field like Climate or PlantGroup that is in both the Sample and TestRanges tables. Fungi/Bacteria/Ecoli is the Test field.

Maybe Access is just different than FMP--i've never worked with Access so i don't know.

[blissland]

I meant to say "Season", not "Climate" in my last post.

[comment]

The first suggestion that was provided to me in this discussion makes the most sense from a design perspective. but it does require me to manually enter 1000 data points

I don't see why, and frankly I am getting tired of this game.

Actually, there is only one suggestion here: make your Samples and Ranges follow the same structure. Either put all types of ranges in the same record, or make a separate record for each sample type. If you ask me, the second option is better - but that's based only on what you did tell us.

[blissland]

Its hardly a game. Its just complex to describe.

The reason I would move data points is that right now low and high values are each located in separate records for each combo of Type, Season, PlantGroup, and Test. If I change the structure, as suggested, to put all the Low and High Values for each Test into one record, then it requires moving about 1000 points, which is fine. Its more elegant too. I just have to wonder how they did it in Access.

You've shown me that there's no way to solve this with calculations so that's what I needed to know--knowing what is not possible helps. Thank you.

[comment]

Moving data is not the same as manually re-entering it - that's what scripting is for.

There IS a way to do this with calculations, but it's so horribly complex I wouldn't even think of it. And it wouldn't solve the real problem, so you would run into the same issue the next time you wanted to add another feature.

[blissland]

i wouldn't know how to script that. i'll just re-enter it.

yeah, horribly complex. i'll pass. i'll just stray from their structure i guess.

thanks.

[comment]

i wouldn't know how to script that.

Actually, that's the easy part. You can even do it manually. Say you have three possible Test values: Bacteria, Fungi and Ecoli, and three other fields Type, Season, PlantGroup. You create a new table with fields:

Type

Season

PlantGroup

Bacteria Low

Bacteria High

Fungi Low

Fungi High

Ecoli Low

Ecoli High

You find the Bacteria test records and you import them into the new table, mapping Low into Bacteria Low and High into Bacteria High. Next, you find the Fungi records and import them while matching on Type, Season and PlantGroup, and mapping Low into Fungi Low and High into Fungi High. Same with Ecoli.

[blissland]

Ah, I see. Very cool.