merit position generation query

[pervej.fac]

hi,
take my cordial respect.I am in so much problem with my software.
I am developing a school management software.In my software i have a table like under table

RegistrationNo	TotalMarks
101	550
102	562
103	550
104	620
105	700

Now I want to generate merit position depending on Registration No.I will input a RegistrationNo in a form textbox query will give merit position of that specific Registration No.Suppose I have inputted RegistrationNo=104 then display merit position 2nd for that RegistrationNo.

If any one give me a sample access project file then i will be much grateful to you.



thanks

[June7]

Sounds like ranking records. This is not easy. Review http://allenbrowne.com/ranking.html

[pervej.fac]

hey
i have already used this page but i have not got my expected result.Could you please give me a solution with attached file?

[pervej.fac]

hey brother,
I have attached my access file.please see.Here merit position column in query is generation merit position.But here merit position 1 has been generated twice but then generated 3 excluding merit position 2.I want 1,1,2 just like merit wise.

[June7]

That query counts how many records beat the value of the current record. I added another record in the posted db (104 & 600). The results are 0, 1, 1, 3 (sorted by TotalValue DESC). Almost there but not quite. Need to assign a ranking by descending TotalValue regardless of CustomerID. Need a dataset that reduces the TotalValue field to distinct values and assigns a sequential rank by descending TotalValue then join that dataset to the qryCustomerValue table linking on the TotalValue field.

Query1: DistinctValue
SELECT DISTINCT qryCustomerValue.TotalValue
FROM qryCustomerValue;

Query2: ValueRank
SELECT TotalValue, (SELECT Count([TotalValue])
FROM DistinctValue AS Dupe
WHERE Dupe.TotalValue > DistinctValue.TotalValue)+1 AS MeritPos
FROM DistinctValue;

Query3:
SELECT qryCustomerValue.CustomerID, qryCustomerValue.TotalValue, ValueRank.MeritPos
FROM ValueRank INNER JOIN qryCustomerValue ON ValueRank.TotalValue = qryCustomerValue.TotalValue;