Ranking

B

Bill

I have the following SQL statement that will rank my data. I am trying to
perform that ranking within age groups. I cannot get it to work. Can anyone
help?

The statement below works but it ranks all the data 1 thru whatever.

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]);


When I try to add the where clause as below, it will not rank by age group.
What am I doing wrong?

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]) where "agegroup <=" &
[agegroup];

Thank you for your help.
 
V

vanderghast

You are already in a kind of where clause, so add extra condition with AND:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =" & [agegroup] );


assuming agegroup is a number. If it is a string:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =""" & [agegroup] & """");





Vanderghast, Access MVP
 
B

Bill

Thank you for your help.

I tried your suggestion and I receive a message that states ) records have
been updated due to record type conversion. Rank is numeric, Agegroup is
text. So I used your STRING example. What am I doing incorrectly.
--
Thank you for your help.


vanderghast said:
You are already in a kind of where clause, so add extra condition with AND:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =" & [agegroup] );


assuming agegroup is a number. If it is a string:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =""" & [agegroup] & """");





Vanderghast, Access MVP


Bill said:
I have the following SQL statement that will rank my data. I am trying to
perform that ranking within age groups. I cannot get it to work. Can
anyone
help?

The statement below works but it ranks all the data 1 thru whatever.

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]);


When I try to add the where clause as below, it will not rank by age
group.
What am I doing wrong?

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]) where "agegroup <=" &
[agegroup];

Thank you for your help.
 
V

vanderghast

My mistake, missing an &. Try:



UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] & " AND agegroup =""" & [agegroup] & """");



Vanderghast, Access MVP


Bill said:
Thank you for your help.

I tried your suggestion and I receive a message that states ) records have
been updated due to record type conversion. Rank is numeric, Agegroup is
text. So I used your STRING example. What am I doing incorrectly.
--
Thank you for your help.


vanderghast said:
You are already in a kind of where clause, so add extra condition with
AND:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =" & [agegroup] );


assuming agegroup is a number. If it is a string:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =""" & [agegroup] & """");





Vanderghast, Access MVP


Bill said:
I have the following SQL statement that will rank my data. I am trying
to
perform that ranking within age groups. I cannot get it to work. Can
anyone
help?

The statement below works but it ranks all the data 1 thru whatever.

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]);


When I try to add the where clause as below, it will not rank by age
group.
What am I doing wrong?

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]) where "agegroup <=" &
[agegroup];

Thank you for your help.
 
B

Bill

That worked great! Thanks.

Is there a way to have the rank set as 1 for all ties or must it be the
higher number.

Example:
Score = 76 Rank = 1
76 Rank = 1
77 Rank = 3

Right now it comes up as
Score = 76 Rank = 2
76 Rank = 2
77 Rank = 3
--
Thank you for your help.


vanderghast said:
My mistake, missing an &. Try:



UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] & " AND agegroup =""" & [agegroup] & """");



Vanderghast, Access MVP


Bill said:
Thank you for your help.

I tried your suggestion and I receive a message that states ) records have
been updated due to record type conversion. Rank is numeric, Agegroup is
text. So I used your STRING example. What am I doing incorrectly.
--
Thank you for your help.


vanderghast said:
You are already in a kind of where clause, so add extra condition with
AND:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =" & [agegroup] );


assuming agegroup is a number. If it is a string:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =""" & [agegroup] & """");





Vanderghast, Access MVP


I have the following SQL statement that will rank my data. I am trying
to
perform that ranking within age groups. I cannot get it to work. Can
anyone
help?

The statement below works but it ranks all the data 1 thru whatever.

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]);


When I try to add the where clause as below, it will not rank by age
group.
What am I doing wrong?

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]) where "agegroup <=" &
[agegroup];

Thank you for your help.
 
V

vanderghast

yes, try:


1+DCOUNT("*","aajgajuniors",
"score<" & [score] & " AND agegroup =""" & [agegroup] & """");


The modifications are:
< instead of <=
1+


Vanderghast, Access MVP

Bill said:
That worked great! Thanks.

Is there a way to have the rank set as 1 for all ties or must it be the
higher number.

Example:
Score = 76 Rank = 1
76 Rank = 1
77 Rank = 3

Right now it comes up as
Score = 76 Rank = 2
76 Rank = 2
77 Rank = 3
--
Thank you for your help.


vanderghast said:
My mistake, missing an &. Try:



UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] & " AND agegroup =""" & [agegroup] & """");



Vanderghast, Access MVP


Bill said:
Thank you for your help.

I tried your suggestion and I receive a message that states ) records
have
been updated due to record type conversion. Rank is numeric, Agegroup
is
text. So I used your STRING example. What am I doing incorrectly.
--
Thank you for your help.


:

You are already in a kind of where clause, so add extra condition with
AND:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =" & [agegroup] );


assuming agegroup is a number. If it is a string:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =""" & [agegroup] & """");





Vanderghast, Access MVP


I have the following SQL statement that will rank my data. I am
trying
to
perform that ranking within age groups. I cannot get it to work.
Can
anyone
help?

The statement below works but it ranks all the data 1 thru whatever.

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]);


When I try to add the where clause as below, it will not rank by age
group.
What am I doing wrong?

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]) where "agegroup <=" &
[agegroup];

Thank you for your help.
 
B

Bill

You are fantastic! That worked GREAT. Thanks a million.
--
Thank you for your help.


vanderghast said:
yes, try:


1+DCOUNT("*","aajgajuniors",
"score<" & [score] & " AND agegroup =""" & [agegroup] & """");


The modifications are:
< instead of <=
1+


Vanderghast, Access MVP

Bill said:
That worked great! Thanks.

Is there a way to have the rank set as 1 for all ties or must it be the
higher number.

Example:
Score = 76 Rank = 1
76 Rank = 1
77 Rank = 3

Right now it comes up as
Score = 76 Rank = 2
76 Rank = 2
77 Rank = 3
--
Thank you for your help.


vanderghast said:
My mistake, missing an &. Try:



UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] & " AND agegroup =""" & [agegroup] & """");



Vanderghast, Access MVP


Thank you for your help.

I tried your suggestion and I receive a message that states ) records
have
been updated due to record type conversion. Rank is numeric, Agegroup
is
text. So I used your STRING example. What am I doing incorrectly.
--
Thank you for your help.


:

You are already in a kind of where clause, so add extra condition with
AND:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =" & [agegroup] );


assuming agegroup is a number. If it is a string:


UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors",
"score<=" & [score] " AND agegroup =""" & [agegroup] & """");





Vanderghast, Access MVP


I have the following SQL statement that will rank my data. I am
trying
to
perform that ranking within age groups. I cannot get it to work.
Can
anyone
help?

The statement below works but it ranks all the data 1 thru whatever.

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]);


When I try to add the where clause as below, it will not rank by age
group.
What am I doing wrong?

UPDATE AAJGAJuniors SET aajgajuniors.rank =
dcount("1","aajgajuniors","score<=" & [score]) where "agegroup <=" &
[agegroup];

Thank you for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top