Let me start by saying that, database developers and scientists comes from different part of the universe and speaks different languages. Thus we must be patient with each other’s inability to understand what we intend to say.
Though I'm not sure on you intention, I give you my best interpretation what you have said so far. That way you get a sample of my language, and can hopefully better express your intent, at next attempt.
I interpret what you write as:
You have one table with measurements made on different "fluorochromes" (I have no idea what that is), over a number of different wavelengths, using different filters. Which could look something like this:
Measurement 1, Fluorochrom, Filter, Wavelength, Value Measurement 2, Fluorochrom, Filter, Wavelength, Value Measurement 3, Fluorochrom, Filter, Wavelength, Value Measurement 4, Fluorochrom, Filter, Wavelength, Value
Then you have another table with "Filter correction data" for different filters over a number of different wavelengths. Which could look something like this:
#1, Filter, Wavelength, Value #2, Filter, Wavelength, Value #3, Filter, Wavelength, Value #4, Filter, Wavelength, Value
What you like to do is to combine each measurement with corresponding filter correction, and then calculate the corrected value, by multiplying the measurement value with filter value, value by value.
If I'm right so fare, you should crate those two tables in FileMaker and then create a relation between them using both "filter" and "wavelength". After that there are a couple different ways to go to create a calculation in the "measurements table".
With best regards Magnus Fransson.
Should I use repeating fields in a simple table, that I could “self-join” based on a user-selected filter-value.
use proper tables for each entity. To figure out what entities you have, start by listing the nouns in your narrative...
Thank you very much for having taken time to try to understand ...
As ever so often, I guess my problem was presented in an overly complicated way. But first of all, to take away some of the magic, a "fluorochrome" is a fluoresceing substance, that emits light upon excitation. Take a glass of Tonic water, expose it to blacklight, and it will fluoresce blue, due to the Quinine it contains .
I could have omitted talking about fluors and filters. Basically, I have a rather large Excel data sheet (100+ columns), and I would like to multiply values in one column by values in another column, row by row (C1=A1xB1;C2=A2xB2; ... etc.), and then SUM the result column (C1+C2+ ..., etc.). There are approximately 100 columns, and each column has between 500 and 1,000 values.
My feeling now is that I should perhaps generate a single Table that contains 1,000 records, one for each row (wavelength) in my data sheet. It should have fields for each of the columns of my data sheet. That would make for some 100 Fields. Self-joining this table using user-entry filter values would create the possibility to do the necessary calculations.
I'll work on this a bit and come back with my impressions ...
Thank you again,
As soon as someone starts a database normalization discussion talking about "self-joins" I get uncomfortable. To me, self-joins are something that comes in much later.
My experience is that persons without database experience, using Excel, often fools them self to believe that they should do every thing in one table. But they are seldom right. (Though it happens.)
Still being unsure, I believe your data should be divided in to three tables.
• The first one, should loosely correspond to your "Rows" and contain about 1000 records, but only a handful fields (including a primary key field).
• The second table, should loosely correspond to your "columns" and contain about 100 records, but only a handful fields (including a primary key field).
• Then there should be a "join-table" that should contain: one primary key field, two foreign key fields (for joining the two previous tables) and a measurement/value field. And about 100 000 records.
This might not be correct, but should hopefully start you thinking about your data in a new, constructive way.
With best regard Magnus Fransson.