Access

[Jonty]

Hi,
Im using access database and have a list of horses with beaten lengths in ascending order and want to put them in a rank order, ie 1,2,3 etc. can anybody help.
Jonty

[robert99]

J,

Could you clarify more? If they are in ascending order they are sorted.
Can sort on any table column or with more complex sorts use a Query to sort out items of same nominal rank into an Answer table.

[Jonty]

Hi Robert,
Thanks for your reply, This is what Im after.
Cuppacocca,BT 2,then 3, then 7 should be in rank 1,2,3 instead of 1,3,2.
Spirit Rising, I want to go BT, 9,then 11,then 13, And rank 1,2,3.
Im not sure what you mean about an answer table.
You can email me at.
johnfneville@aol.com

Many Thanks.
Jonty

NAME	BT	Rank
CUPPACOCOA	2	1
CUPPACOCOA	3	3
CUPPACOCOA	7	2
SPIRIT RISING	11	1
SPIRIT RISING	9	3
SPIRIT RISING	13	2

[GotaFancy?]

If you start a new query, putting in the fields Name, BT and Rank, then you can sort by Name and Rank to give you what you need.....

If the table name was "data", then the SQL would be....

SELECT Data.Name, Data.BT, Data.Rank
FROM Data
ORDER BY Data.Name, Data.Rank;

This gives me the following output,

Query1

Name	BT	Rank
CUPPACOCOA	2	1
CUPPACOCOA	7	2
CUPPACOCOA	3	3
SPIRIT RISING	11	1
SPIRIT RISING	13	2
SPIRIT RISING	9	3

[Jonty]

Hi Gotafancy,
I.ve got the name and BT but I haven't got the Rank cell, but I want to put them in Rank order.
Thanks Jonty

[GotaFancy?]

Are the ranks in your post 3 correct then? The rank should just be the order of the BT field? There doesn't seem to be any correlation between your rank values and the BT values - so if you haven't already got it, I'm not sure what exactly you're trying to do ......

My best guess is that you just want to sort by Name and BT? (in which case the Rank is unnecessary?

So it would just be this?


Quote:

SELECT Data.Name, Data.BT
FROM Data
ORDER BY Data.Name, Data.BT;

[Jonty]

Hi Gotafancy,
The data comes in a random order and I want to put them in a rank order so I can ask for Rank 1 or Rank 3 or Rank 2 etc.
But I need them in Rank 1,2 3 order
Jonty

[GotaFancy?]

But ranking according to what?

Your table is:

NAME	BT	Rank
CUPPACOCOA	2	1
CUPPACOCOA	3	3
CUPPACOCOA	7	2
SPIRIT RISING	11	1
SPIRIT RISING	9	3
SPIRIT RISING	13	2

Should it be

NAME	BT	Rank
CUPPACOCOA	2	1
CUPPACOCOA	3	2
CUPPACOCOA	7	3
SPIRIT RISING	11	2
SPIRIT RISING	9	1
SPIRIT RISING	13	3

If it's not, them I'm afraid I really am lost........

[Jonty]

Hi Gotafancy,
This might be better

NAME	BT
CUPPACOCOA	2
CUPPACOCOA	3
CUPPACOCOA	7
SPIRIT RISING	1
SPIRIT RISING	4
SPIRIT RISING	6

Both the Horses BT are in ascending order, I want to put A cell Rank so I can ask for Rank order 1 or 2 or 3, Or put another way If I want Say Cuppacoccoa Bt 3, And Spirit Rising BT 4 I ask for Rank 2