how do I use the rank function in access similar to excel

[Guest]

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.

 

[Duane Hookom]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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]);

 

[Guest]

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.

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

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]

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

--
Duane Hookom
MS Access MVP

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.

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

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.


:

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


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


:

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


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.

 

[Guest]

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.

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

--
Duane Hookom
MS Access MVP

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.

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


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.


:

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


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


:

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


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.

 

[Bill]

Duane, this has helped me tremendously with a similiar problem. I , however,
need to have the ranking done by marks as in your Update statement, but I
also need it by class within school. Can you help me accomplish that?
--
Thank you for your help.

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

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.

 

[Bill]

Duane, this has helped me tremendously with a similiar problem. I , however,
need to have the ranking done by marks as in your Update statement, but I
also need it by class within school. Can you help me accomplish that?
--
Thank you for your help.

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

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.