ranking the records

[subs]

i have a table like this
name dept sales rank
ram purchase 230 1
bill purchase 120 2
mol purchase 40 3
steve purchase 24 4
ram logistics 89 1
ram logistics 23 2
bill logistics 12 3
mol logistics 4 4
steve logistics 2 5

I already have the first three columns in one of my tables. I want to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank

please help this is urgent. I would really appreciate

 

[Marshall Barton]

i have a table like this
name dept sales rank
ram purchase 230 1
bill purchase 120 2
mol purchase 40 3
steve purchase 24 4
ram logistics 89 1
ram logistics 23 2
bill logistics 12 3
mol logistics 4 4
steve logistics 2 5

I already have the first three columns in one of my tables. I want to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank

SELECT [name], dept, sales,
(SELECT Count(*) FROM table As X
WHERE X.dept = table.dept
And X.sales >= table.sales) As rank
FROM table

 

[subs]

i have a table  like this
name  dept          sales    rank
ram   purchase      230     1
bill     purchase     120     2
mol    purchase     40      3
steve  purchase    24      4
ram    logistics      89     1
ram    logistics      23     2
bill     logistics      12     3
mol    logistics      4       4
steve  logistics    2       5

I  already have the first three columns in one of my tables. I want to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank

SELECT [name], dept, sales,
                                (SELECT Count(*) FROM table As X
                                 WHERE X..dept = table.dept
                                                And X.sales >= table.sales) As rank
FROM table

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank you so much it worked. But what if i need the rankings change
after four different columns change in values. like here above
rankings change once the field dept changes. Say suppose i need the
rankings change once four different columns change - like

origin st origin city destn st destn
city carrier price Rank

A B C
D X 40 1
A B C
D Y 80 2
A B C
D Z 90 3

A1 B1 C1
D1 X 89 1
A1 B1 C1
D1 Y 99 2

 

[Marshall Barton]

i have a table  like this
name  dept          sales    rank
ram   purchase      230     1
bill     purchase     120     2
mol    purchase     40      3
steve  purchase    24      4
ram    logistics      89     1
ram    logistics      23     2
bill     logistics      12     3
mol    logistics      4       4
steve  logistics    2       5

I  already have the first three columns in one of my tables. I want to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank

SELECT [name], dept, sales,
                                (SELECT Count(*) FROM table As X
                                 WHERE X.dept = table.dept
                                                And X.sales >= table.sales) As rank
FROM table


- Show quoted text -

Thank you so much it worked. But what if i need the rankings change
after four different columns change in values. like here above
rankings change once the field dept changes. Say suppose i need the
rankings change once four different columns change - like

origin st origin city destn st destn
city carrier price Rank

A B C
D X 40 1
A B C
D Y 80 2
A B C
D Z 90 3

A1 B1 C1
D1 X 89 1
A1 B1 C1
D1 Y 99 2

Just change the
WHERE X.dept = table.dept
  And X.sales >= table.sales
to

WHERE X.[origin st] = table.[origin st]
And X.[origin city] = table.[origin city]
And X.[destn st] = table.[destn st]
And X.[destn city] = table.[destn city]
  And X.sales >= table.sales

 

[Steve S]

How does this code resolve ties?

subs wrote:
i have a table like this
name dept sales rank
ram purchase 230 1
bill purchase 120 2
mol purchase 40 3
steve purchase 24 4
ram logistics 89 1
ram logistics 23 2
bill logistics 12 3
mol logistics 4 4
steve logistics 2 5

I already have the first three columns in one of my tables. I want to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank

SELECT [name], dept, sales,
(SELECT Count(*) FROM table As X
WHERE X.dept = table.dept
And X.sales >= table.sales) As rank
FROM table


- Show quoted text -

Thank you so much it worked. But what if i need the rankings change
after four different columns change in values. like here above
rankings change once the field dept changes. Say suppose i need the
rankings change once four different columns change - like

origin st origin city destn st destn
city carrier price Rank

A B C
D X 40 1
A B C
D Y 80 2
A B C
D Z 90 3

A1 B1 C1
D1 X 89 1
A1 B1 C1
D1 Y 99 2

Just change the
WHERE X.dept = table.dept
And X.sales >= table.sales
to

WHERE X.[origin st] = table.[origin st]
And X.[origin city] = table.[origin city]
And X.[destn st] = table.[destn st]
And X.[destn city] = table.[destn city]
And X.sales >= table.sales

 

[Michel Walsh]

It ranks them up. With values likes: 10 25 25 40, the respective ranks
would be 1 3 3 4

You can use the primary key to break any tie (on the Sales value), like:

-----------------------------
SELECT X.[origin st] , X.[origin city], X.[destn st] , X.[destn city],
COUNT(*) AS rank

FROM tableName AS X
INNER JOIN tableName AS Y
ON X.[origin st] = Y.[origin st]
AND X.[origin city] = Y.[origin city]
AND X.[destn st] = Y.[destn st]
AND X.[destn city] = Y.[destn city]
AND (
( X.sales > Y.sales )
OR
( X.Sales=Y.Sales AND X.pk >= Y.pk )
)

GROUP BY X.[origin st], X.[origin city], X.[destn st] , X.[destn city]
---------------------------------


or use any other tie-breaker test.


Note that this query uses a join rather than a sub-query, but is not
editable graphically.



Vanderghast, Access MVP

How does this code resolve ties?

subs wrote:
i have a table like this
name dept sales rank
ram purchase 230 1
bill purchase 120 2
mol purchase 40 3
steve purchase 24 4
ram logistics 89 1
ram logistics 23 2
bill logistics 12 3
mol logistics 4 4
steve logistics 2 5

I already have the first three columns in one of my tables. I want
to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank

SELECT [name], dept, sales,
(SELECT Count(*) FROM table As X
WHERE X.dept = table.dept
And X.sales >=
table.sales) As rank
FROM table


- Show quoted text -
Thank you so much it worked. But what if i need the rankings change
after four different columns change in values. like here above
rankings change once the field dept changes. Say suppose i need the
rankings change once four different columns change - like

origin st origin city destn st destn
city carrier price Rank

A B C
D X 40 1
A B C
D Y 80 2
A B C
D Z 90 3

A1 B1 C1
D1 X 89 1
A1 B1 C1
D1 Y 99 2

Just change the
WHERE X.dept = table.dept
And X.sales >= table.sales
to

WHERE X.[origin st] = table.[origin st]
And X.[origin city] = table.[origin city]
And X.[destn st] = table.[destn st]
And X.[destn city] = table.[destn city]
And X.sales >= table.sales