I have made a very basic programme in Access which has marks of different

students in different subjects. I want to rank these students as per their

marks. As of now everytime I have to resort them and then manually type the

Rank. Is there a better way wherein the software ranks the students depending

on the number of students?

I do not want to use excel as there I have to specify the cell Range in the

rank formula. If the number of students vary I have to rewrite the formula

again.

Can you provide some information on your table structure and desired output?

Normally since a rank can be calculated and may change over time, it is not

stored in a table.



--

Duane Hookom

MS Access MVP





"koty" <koty@discussions.microsoft.com> wrote in message

news:FFCEA2C9-6687-4B0F-B17E-BBB87A28970F@microsoft.com...

>I have made a very basic programme in Access which has marks of different

> students in different subjects. I want to rank these students as per their

> marks. As of now everytime I have to resort them and then manually type

> the

> Rank. Is there a better way wherein the software ranks the students

> depending

> on the number of students?

> I do not want to use excel as there I have to specify the cell Range in

> the

> rank formula. If the number of students vary I have to rewrite the formula

> again.

The Table/ querry  has columns for Student Name, Mathsmarks, Sciencemarks.

The querry has a field for Rank. desired output is as below:



Student	Mathmarks	science marks	Total Marks	Rank

aaa	98	56	154	2

bbb	78	67	145	3

ccc	80	86	166	1





"Duane Hookom" wrote:



> Can you provide some information on your table structure and desired output?

> Normally since a rank can be calculated and may change over time, it is not

> stored in a table.

>

> --

> Duane Hookom

> MS Access MVP

>

>

> "koty" <koty@discussions.microsoft.com> wrote in message

> news:FFCEA2C9-6687-4B0F-B17E-BBB87A28970F@microsoft.com... 

>

>

>

Are you expecting to store the Rank? Do you have the opportunity to

normalize your table structure so that each mark creates its own record?



Are you hoping to simply display this in a report? Reports have an easy

method of displaying a "sequence" number with each record.



--

Duane Hookom

MS Access MVP





"koty" <koty@discussions.microsoft.com> wrote in message

news:11CDD40C-D6BB-4999-A2A1-119409E092E6@microsoft.com...[vbcol=seagreen]

> The Table/ querry  has columns for Student Name, Mathsmarks, Sciencemarks.

> The querry has a field for Rank. desired output is as below:

>

> Student Mathmarks science marks Total Marks Rank

> aaa 98 56 154 2

> bbb 78 67 145 3

> ccc 80 86 166 1

>

>

> "Duane Hookom" wrote:

> 

I just want to make a formula such that the Rank is generated and it stays

with the record forever.

I did not understand the meaning of Normalizing the Table Structure.



I also tried using the " Number each detail record in a group or Report" as

mentioned under the Sequence number Help as you mentioned.

But it generates the rank in the Report only. If I now have to resort the

querry in a different order, I want the original Rank to be retained.





"Duane Hookom" wrote:



> Are you expecting to store the Rank? Do you have the opportunity to

> normalize your table structure so that each mark creates its own record?

>

> Are you hoping to simply display this in a report? Reports have an easy

> method of displaying a "sequence" number with each record.

>

> --

> Duane Hookom

> MS Access MVP

>

>

> "koty" <koty@discussions.microsoft.com> wrote in message

> news:11CDD40C-D6BB-4999-A2A1-119409E092E6@microsoft.com... 

>

>

>

I don't agree with storing values that can be calculated however, you can

create an update query with SQL like:



UPDATE tblMarks

SET tblMarks.Rank = DCount("*", "tblMarks","MathMarks+ScienceMarks >=" &

[MathMarks]+[ScienceMarks]);





--

Duane Hookom

MS Access MVP





"koty" <koty@discussions.microsoft.com> wrote in message

news:661C73D5-F7EB-4FDB-AD4A-3DA34D2B1DE8@microsoft.com...[vbcol=seagreen]

>I just want to make a formula such that the Rank is generated and it stays

> with the record forever.

> I did not understand the meaning of Normalizing the Table Structure.

>

> I also tried using the " Number each detail record in a group or Report"

> as

> mentioned under the Sequence number Help as you mentioned.

> But it generates the rank in the Report only. If I now have to resort the

> querry in a different order, I want the original Rank to be retained.

>

>

> "Duane Hookom" wrote:

> 

I tried you suggestion but every time I got a Syntax Error.May be if you

could be more elaborate _

Also I do not want to Store values which can be calculated. i.e. If rank

value is going to remain constant everytime I sort the data in a different

order my requirement is met.

To be precise I am looking for the exact functionality of Rank function in

Excel. The formula should keep extending the Range of selected cells as I

keep adding the Data.

Sorry to bother u but I am an absolute novice. So be specific if u can.

Thanx a ton in advance.



"Duane Hookom" wrote:



> I don't agree with storing values that can be calculated however, you can

> create an update query with SQL like:

>

> UPDATE tblMarks

> SET tblMarks.Rank = DCount("*", "tblMarks","MathMarks+ScienceMarks >=" &

> [MathMarks]+[ScienceMarks]);

>

>

> --

> Duane Hookom

> MS Access MVP

>

>

> "koty" <koty@discussions.microsoft.com> wrote in message

> news:661C73D5-F7EB-4FDB-AD4A-3DA34D2B1DE8@microsoft.com... 

>

>

>

What are you exact table/query and field names? What is the exact SQL that

you tried?



--

Duane Hookom

MS Access MVP





"koty" <koty@discussions.microsoft.com> wrote in message

news:367F8FA7-9623-4F86-90B9-474489AA5773@microsoft.com...[vbcol=seagreen]

>I tried you suggestion but every time I got a Syntax Error.May be if you

> could be more elaborate _

> Also I do not want to Store values which can be calculated. i.e. If rank

> value is going to remain constant everytime I sort the data in a different

> order my requirement is met.

> To be precise I am looking for the exact functionality of Rank function in

> Excel. The formula should keep extending the Range of selected cells as I

> keep adding the Data.

> Sorry to bother u but I am an absolute novice. So be specific if u can.

> Thanx a ton in advance.

>

> "Duane Hookom" wrote:

> 

Hi!

I visited the following page

http://support.microsoft.com/kb/q120608/

which has given a example (example no. 2). The structure of the formula was

almost similar to yours. While building the expression I used [CountOfTotal]

instead of [Total]

itself. That was resulting in all students being ranked as 1.

Now the problem is solved.

Thanx you very much once again.



"Duane Hookom" wrote:



> What are you exact table/query and field names? What is the exact SQL that

> you tried?

>

> --

> Duane Hookom

> MS Access MVP

>

>

> "koty" <koty@discussions.microsoft.com> wrote in message

> news:367F8FA7-9623-4F86-90B9-474489AA5773@microsoft.com... 

>

>

>