Show higher values AND smaller than current, using different columns

[luiz_damascena]

Imagine the following scenario: a set of records as below

Field1, Field2, Field3, Field4

     1,      2,      3,      4

     5,      6,      7,      8

     9,     10,     11,     12

    13,     14,     15,     16

    17,     18,     19,     20

With the applied filters, the information visualized is from the third line: 9,10,11,12

I want to know, among all the values in my recordset,

• What is the next value down from the Field 3 column. The answer would be 7.
• What is the next value up from the Field 4 column. The answer would be 16.

Answers will be displayed in the same query, without changing my current filtering.

It is as if I wanted to display the current value and also the maximum value using the Max() function.

But I want to display the next higher or lower value for different columns, as the case may be.

Is it possible?

Thank you in advance,

Luiz Carlos.

[sunny_talwar]

Will you be elaborate on where exactly are you hoping to see 7 and 16? And it will be always Field 3 down and Field 4 up? or is there another way user are able to determine this?

[Anonymous]

Hi,

Something like this

Regards,

[luiz_damascena]

I'll try to make the question clearer ...

Let us assume that Field1 is the product code (a car, for example), Field2 is a month, Field3 is the average consumption, Field4 is the highest speed achieved, Field5 is the longest distance traveled, etc.

That is, each field after Field2 are measured quantities relative to my car, in a given month.

Looking at the information of car 1 in March, I also want to see what the average consumption is closer to mine, up and down; at the same time, I want to know the highest speed, up and down, always considering all the cars in the same month.

I can not use classification. Assuming a possible index using Field1, the desired information (in relation to my average consumption, which is immediately below mine?) will not be in the "top line". It can be anywhere, in any line.

I return to the example of the maximum and minimum: displaying my speed, to know the highest and lowest speeds recorded, considering all vehicles, it would be enough to use max () and min ().

But I do not want the maximum value: I want the value closest to mine, which can be in any line.

I hope I have elaborated better ...

Thank you in advance.

Luiz Carlos

[luiz_damascena]

I tried to elaborate a better question... please, read it.

[luiz_damascena]

Interesting approach, Mauri, but not exactly what I want. But thank you! I tried to elaborate my question better... please, read it.

[sunny_talwar]

That is, each field after Field2 are measured quantities relative to my car, in a given month.

So, your car is not part of the data because everything is relative to your car?

Looking at the information of car 1 in March

Now car 1 is your car? but this is not part of the database or is it?

I am feeling lost, would you be able to share a sample or sample data and give out exactly what you are hoping to get out of it?

[sasiparupudi1]

Please attach a sample

[luiz_damascena]

Right now, grateful for your attention and patience ...

"My car" is part of the data. Each car in the database has its own information.

Here's a sample data:

Fields

carCode, monitoredMonth, averageConsumption, highestSpeed​, distanceTraveled

Values

1, Jan, 14.2, 100, 240

2, Jan,  7.7,  85, 400

3, Jan, 12.1, 120, 200

4, Jan,  8.5,  92, 130

5, Jan, 10.1,  70, 350

As extra information, the units of measure of the columns: averageConsumption km / l, highestSpeed ​​km / h, distanceTraveled km.

Seeing information from car 3, I see that averageConsumption is 12.1 km / l, highestSpeed 120 km / h, distanceTraveled 200 km.

Also, I want to display the averageConsumption immediately lower than that of car 3, 12.1 Km / l, which will be on the screen. The answer will be 10.1 km / l (information linked to car 5)

I also want to show the distance traveled immediately greater than that of the car, 200 Km, which will be on the screen. The answer will be 240 km (information linked to car 1).

[sunny_talwar]

This?