How to find biggest values below 6000 and above 6000 from all fields?

[R2D2]

Hi!

 

I have a database with 7 columns and several rows containing numbers (see the picture). I would need to find the biggest numbers in whole found set below 6000 (1000 - 5999) and above 6000 (>6000). For example 1017 and 6037. I've tried different approaches but now I have to give up. Probably the solution is vey simple but I just cannot figure it out now.☹️ Any ideas?

Edited December 30, 2019 by R2D2

[comment]

Consider changing your structure to a relational one: instead of 7 columns (which appear to be sparsely populated) use 7 (or any other number of) records in a child table. This will allow you to deal with just one set of numbers instead of 7 - and is likely to make a lot of other tasks easier too.

To find the largest number, you could perform a find for values in the given range, then sort the records and get the value from the last (or the first) record. Or use a summary field defined as Maximum of the value field. 

Achieving the same thing with your current structure would be very cumbersome. 

 

[R2D2]

Thank you comment! I need to keep this one table "non-relational" because it is kind of a "base table" to a numerous other relational tables. Of course this could be done as you said, but then I'll be completely lost among all the relations😫

I found a clumsy but working solution: I created 7 Summary Fields (one for each column) with Maximum of found set. Then I made a script with 14 repetitive Finds with search criterias <6000 and above 6000, and picked after each Find the maximum number to a variable. Finally I listed those variables and picked the winners.

As I said, dirty code, but it works