Ranking

[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.

 

[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

 

[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.

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.

 

[vanderghast]

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.

 

[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.

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.

 

[vanderghast]

yes, try:


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


The modifications are:
< instead of <=
1+


Vanderghast, Access MVP

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.

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.

 

[Bill]

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

yes, try:


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


The modifications are:
< instead of <=
1+


Vanderghast, Access MVP

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.

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.