If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ranking
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. |
#2
|
|||
|
|||
Ranking
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" wrote in message ... 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. |
#3
|
|||
|
|||
Ranking
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" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
Ranking
My mistake, missing an &. Try:
UPDATE AAJGAJuniors SET aajgajuniors.rank = dcount("1","aajgajuniors", "score=" & [score] & " AND agegroup =""" & [agegroup] & """"); Vanderghast, Access MVP "Bill" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#5
|
|||
|
|||
Ranking
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" wrote: My mistake, missing an &. Try: UPDATE AAJGAJuniors SET aajgajuniors.rank = dcount("1","aajgajuniors", "score=" & [score] & " AND agegroup =""" & [agegroup] & """"); Vanderghast, Access MVP "Bill" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#6
|
|||
|
|||
Ranking
yes, try:
1+DCOUNT("*","aajgajuniors", "score" & [score] & " AND agegroup =""" & [agegroup] & """"); The modifications a instead of = 1+ Vanderghast, Access MVP "Bill" wrote in message ... 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" wrote: My mistake, missing an &. Try: UPDATE AAJGAJuniors SET aajgajuniors.rank = dcount("1","aajgajuniors", "score=" & [score] & " AND agegroup =""" & [agegroup] & """"); Vanderghast, Access MVP "Bill" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
#7
|
|||
|
|||
Ranking
You are fantastic! That worked GREAT. Thanks a million.
-- Thank you for your help. "vanderghast" wrote: yes, try: 1+DCOUNT("*","aajgajuniors", "score" & [score] & " AND agegroup =""" & [agegroup] & """"); The modifications a instead of = 1+ Vanderghast, Access MVP "Bill" wrote in message ... 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" wrote: My mistake, missing an &. Try: UPDATE AAJGAJuniors SET aajgajuniors.rank = dcount("1","aajgajuniors", "score=" & [score] & " AND agegroup =""" & [agegroup] & """"); Vanderghast, Access MVP "Bill" wrote in message ... 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" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|