Duplicate RANK() in access form, how??..

  • Thread starter Thread starter howren
  • Start date Start date
H

howren

I want to create an interactive access form.
As marks are entered the positions are shown
automatically. It's very hard to create that
in Access but very easy in Excel using the function
RANK(). The problem is I need to do it in Access but
after 2 weeks I still don't know how to achieve it.

Please help me.

This is what I am trying to do in MS Access:

Name Marks Pos
---------------
Jane 50 2
Aby 61 1
Chang 44 3

As the marks are entered or changed the pos updates
itself automatically. Any idea how? Take note that I
want to do it in an Access form. There must not be any
sorting to the names, the list is as it is in the table.

Send solution to (e-mail address removed)
THANKS
 
howren said:
I want to create an interactive access form.
As marks are entered the positions are shown
automatically. It's very hard to create that
in Access but very easy in Excel using the function
RANK(). The problem is I need to do it in Access but
after 2 weeks I still don't know how to achieve it.

Please help me.

This is what I am trying to do in MS Access:

Name Marks Pos
---------------
Jane 50 2
Aby 61 1
Chang 44 3

As the marks are entered or changed the pos updates
itself automatically. Any idea how? Take note that I
want to do it in an Access form. There must not be any
sorting to the names, the list is as it is in the table.

Send solution to [address snipped]
Click to expand...

No, sorry, but when you ask your question in the newsgroup, that's where
you look for your answer. I snipped your address from the quoted
message in hopes of protecting you from address-harvesting programs used
by spammers and viruses; it's probably too late, but good luck.

Now for your question. Suppose your form is based on a table called
"tblMarks". Then you could have a calculated text box "Pos" on your
form, with this expression as its ControlSource:

=1+DCount("*","tblMarks","Marks>" & [Marks])

You also need a bit of code to tell Access to recalculate the text box
whenever a record is updated or deleted on the form. You would use the
form's AfterUpdate event and AfterDelConfirm events to do that:

Private Sub Form_AfterDelConfirm(Status As Integer)
Me.Recalc
End Sub

Private Sub Form_AfterUpdate()
Me.Recalc
End Sub

There is one potential flaw in this method. Individuals with the same
Marks will be assigned the same Pos, which is probably what you want,
but the following individuals won't be "pushed" down by the number of
duplicate Pos values. For example, you could get this:

Name, Marks, Pos
-------------------
Jane, 50, 2
Aby, 61, 1
Chang, 44, 3
John, 50, 2

not this:

Name, Marks, Pos
-------------------
Jane, 50, 2
Aby, 61, 1
Chang, 44, 4
John, 50, 2

There are ways around this, but they are much more complicated.
 
I think you have this reversed. If I'm not mistaken, using your formula,
Chang would be pushed to the position of 4 not three.
Your formula would assign a value to 'Pos' based on the number of records
with with higher values in the 'Marks' field. That count would include
duplicates.
 
Tom Stoddard said:
I think you have this reversed. If I'm not mistaken, using your
formula, Chang would be pushed to the position of 4 not three.
Your formula would assign a value to 'Pos' based on the number of
records with with higher values in the 'Marks' field. That count
would include duplicates.
Click to expand...

By golly, you're right. I somehow convinced myself there was a problem
when there wasn't.
 
Back
Top