Ranking on Sub categories

[Chris]

Tom,

I have read many posts on this site look at the following info:

Table: NFL Team Sls

Store Sls Team Rnk
a 100 Eagles 1
a 150 Patriots 2
b 75 Eagles 1
b 300 Patriots 2
c 125 Eagles 1
c 250 Patriots 2


This is what I am looking for.

Note the Rank field is not on the table...I tried to use the following SQL,
but it ranks everyone.

ALTER TABLE [NFL team sls] ADD COLUMN Rank counter;

I believe that this could work, but I would need to put some information
about each change in store.

Thanks.

 

[Marshall Barton]

I have read many posts on this site look at the following info:

Table: NFL Team Sls

Store Sls Team Rnk
a 100 Eagles 1
a 150 Patriots 2
b 75 Eagles 1
b 300 Patriots 2
c 125 Eagles 1
c 250 Patriots 2


This is what I am looking for.

Note the Rank field is not on the table...I tried to use the following SQL,
but it ranks everyone.

ALTER TABLE [NFL team sls] ADD COLUMN Rank counter;

I believe that this could work, but I would need to put some information
about each change in store.

No, that won't even come close to ranking.

Generally you do not want to put the ranking number in a
table field, rather put it in a calculated field in a query.
(If you do want it in the table, you should just enter the
rank number manually.)

SELECT Store, Sls, Team,
(SELECT Count(*)
FROM [NFL Team Sls] AS T
WHERE T.Store = [NFL Team Sls].Store
AND T.Sls <= [NFL Team Sls].Sls
) AS Rnk
FROM [NFL Team Sls]

 

[Chris]

Thanks this works...IF I want to reverse the ranking order what function
would I need to add?

I have read many posts on this site look at the following info:

Table: NFL Team Sls

Store Sls Team Rnk
a 100 Eagles 1
a 150 Patriots 2
b 75 Eagles 1
b 300 Patriots 2
c 125 Eagles 1
c 250 Patriots 2


This is what I am looking for.

Note the Rank field is not on the table...I tried to use the following SQL,
but it ranks everyone.

ALTER TABLE [NFL team sls] ADD COLUMN Rank counter;

I believe that this could work, but I would need to put some information
about each change in store.

No, that won't even come close to ranking.

Generally you do not want to put the ranking number in a
table field, rather put it in a calculated field in a query.
(If you do want it in the table, you should just enter the
rank number manually.)

SELECT Store, Sls, Team,
(SELECT Count(*)
FROM [NFL Team Sls] AS T
WHERE T.Store = [NFL Team Sls].Store
AND T.Sls <= [NFL Team Sls].Sls
) AS Rnk
FROM [NFL Team Sls]

 

[Marshall Barton]

Just change the <= to >=
--
Marsh
MVP [MS Access]

Thanks this works...IF I want to reverse the ranking order what function
would I need to add?

Generally you do not want to put the ranking number in a
table field, rather put it in a calculated field in a query.

SELECT Store, Sls, Team,
(SELECT Count(*)
FROM [NFL Team Sls] AS T
WHERE T.Store = [NFL Team Sls].Store
AND T.Sls <= [NFL Team Sls].Sls
) AS Rnk
FROM [NFL Team Sls]

 

[Germeny Santos]

Hi guys,
I am trying to use the same formula in my database and I am getting the
following error message:
“You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query’s FROM clause. Revise the
SELECT statement of the subquery to request only one fieldâ€

Can any of you help me to make it work?

Thank you,
Germeny Santos

I have read many posts on this site look at the following info:

Table: NFL Team Sls

Store Sls Team Rnk
a 100 Eagles 1
a 150 Patriots 2
b 75 Eagles 1
b 300 Patriots 2
c 125 Eagles 1
c 250 Patriots 2


This is what I am looking for.

Note the Rank field is not on the table...I tried to use the following SQL,
but it ranks everyone.

ALTER TABLE [NFL team sls] ADD COLUMN Rank counter;

I believe that this could work, but I would need to put some information
about each change in store.

No, that won't even come close to ranking.

Generally you do not want to put the ranking number in a
table field, rather put it in a calculated field in a query.
(If you do want it in the table, you should just enter the
rank number manually.)

SELECT Store, Sls, Team,
(SELECT Count(*)
FROM [NFL Team Sls] AS T
WHERE T.Store = [NFL Team Sls].Store
AND T.Sls <= [NFL Team Sls].Sls
) AS Rnk
FROM [NFL Team Sls]