Create a ranking

  • Thread starter Thread starter Hanksor
  • Start date Start date
H

Hanksor

I have a table where one of the fields is "Total". I would like to be able
to create a ranking for each total value.

Example;

Total Ranking
300 1
200 2
100 3

and so on........

Is this possible? Each line item has a different customer name.....

Any help will be appreciated.
 
Yes, actually I did. I don't need to update a record though and I wouldn't
have a clue on how to pare down the examples given. All I need to do is
take a column of numbers and give them a ranking from highest to lowest.
 
Try this SQL

SELECT RankingCopy1.SelectedField, RankingCopy1.AnotherField, Count(*) AS
Rank
FROM Ranking AS RankingCopy1 INNER JOIN Ranking AS RankingCopy2 ON
RankingCopy1.SelectedField >= RankingCopy2.SelectedField
GROUP BY RankingCopy1.SelectedField, RankingCopy1.AnotherField;

You need a table called Ranking, with two fields called SelectedField and
AnotherField the ranking is based on the values in SelectedField.

You should be able to modify this for your situation, note the SQL cannot be
created in the Query Designer grid, use the SQL view directly. You could do
it in the Query Designer and then return to SQL view to add the > in the
FROM clause.
 
Thanks for the info. This should help.......
Craig Alexander Morrison said:
Try this SQL

SELECT RankingCopy1.SelectedField, RankingCopy1.AnotherField, Count(*) AS
Rank
FROM Ranking AS RankingCopy1 INNER JOIN Ranking AS RankingCopy2 ON
RankingCopy1.SelectedField >= RankingCopy2.SelectedField
GROUP BY RankingCopy1.SelectedField, RankingCopy1.AnotherField;

You need a table called Ranking, with two fields called SelectedField and
AnotherField the ranking is based on the values in SelectedField.

You should be able to modify this for your situation, note the SQL cannot be
created in the Query Designer grid, use the SQL view directly. You could do
it in the Query Designer and then return to SQL view to add the > in the
FROM clause.
Click to expand...
 
Back
Top