need help overcoming bad database design
I'm new to FileMaker Pro Advanced but have had experience with other databases sometime ago and am slowly coming up to speed. I have inherited a badly designed database that was originally imported I think from dbase.
Anyway, I'm hoping someone can help with my problem:
I have a table that where each record has some basic information and then 24 number fields containing various values. My problem is I somehow need to effectively 'sort' those 24 fields in each record in ascending order. That is, sort within the record (the other records are irrelevant for this purpose).
I know it should have been designed with a single record for each of the 24 values but it wasn't.
What I'm thinking is I need to add another 24 fields on each record that effectively ranks the original 24 fields. i.e. - if the field with the lowest value is field 22 then the first of my new 24 fields would contain the value 22 and so on.
The other option is if I can somehow create a new table that breaks each record into 24 separate records to make things super simple.
Can I achieve either of these options with FileMaker Pro Advanced? If so, how?
Or is there some other solution?