A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ranking



 
 
Thread Tools Display Modes
  #1  
Old July 8th, 2009, 12:35 PM posted to microsoft.public.access.queries
Bill
external usenet poster
 
Posts: 1,009
Default 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  
Old July 8th, 2009, 12:42 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old July 9th, 2009, 01:36 AM posted to microsoft.public.access.queries
Bill
external usenet poster
 
Posts: 1,009
Default 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  
Old July 9th, 2009, 03:08 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old July 10th, 2009, 12:26 PM posted to microsoft.public.access.queries
Bill
external usenet poster
 
Posts: 1,009
Default 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  
Old July 10th, 2009, 01:30 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old July 10th, 2009, 01:54 PM posted to microsoft.public.access.queries
Bill
external usenet poster
 
Posts: 1,009
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.