Query to sort in reverse order

[jaryszek]

Hi,



I want to and new column to query and sort it in reverse order.

I have numbers in table:

Table1.Numbers: 1, 5, 10, 15
and want to write query to add ReverseNumberCalculated column: 15, 10, 5, 1

How can i do it?

Write subquery or something?

Best,
Jacek

[jaryszek]

One more thing. For each number i have server:
S1, S2, S3, S4.

For S1 = 1, S2 = 5, S3 = 10, S4 = 15
So it should be S1 = 15; S2 = 10; S3 = 5; S4 = 1

Best,
Jacek

[jaryszek]

It should be like this:

[isladogs]

You don't need to add an extra column
Create a query and sort by Number DESC

BUT CHANGE the field name - Number is a RESERVED word. Not sure about SERVER???

[jaryszek]

Hi ridders52,

how are you? than you

I can create the query and sort it by desc number but i want to have output like in the picture.
So i want to have in one record for S1= Number = 1 and ReversedNumber = 15...

Best,
Jacek

[Gicu]

In your new calculated field use ABS(0-[Number]) (replace the name of the number field as per ridders52 suggestion).

Cheers,
Vlad

[jaryszek]

Hi Vlad,

i do not understand unfortunately.
Can you give some example?

ABS is creating absolute number, so from -3 result will be 3.
How this is connected to this case?

Best,
Jacek

[Gicu]

Sorry, you're right,I rushed to the conclusion. I think you need to use a couple of ranking queries, please have a look at the attached file.

Sorry again for the first post!
Vlad

[jaryszek]

Hi Vlad,

great thank you!

Understand all except working of subquery:

select Count(*) from tblServer 
              where Number<tblServ.Number 
                  and 
                        Server=tblServer.Server 
           ) +1 AS RankASC

how can i imagine to see the solution in my head ?

Best,
Jacek

[Gicu]

Hi Jacek!

Not sure if it is a question or a comment. Here are some links that might help:

https://answers.microsoft.com/en-us/...c-68b599b31bf5
https://stackoverflow.com/questions/...s-access-query

Cheers,
Vlad

[jaryszek]

It is a question.

how this count is working here?

I am counting all where number < number?
what does it mean?

Jacek

[Gicu]

Hi Jacek,

It is doing a ranking based on the number field. So for the first server (S1) with the number of 1 the count of servers with a number less than 1 is 0 (so we add 1 to give you the "rank" of 1). And so on until the last.

Cheers,
Vlad

[jaryszek]

aa great!

thank you
Now i am understanding !

Best,
Jacek