Record Ranking

[Lotto]

Seems too simple to be so difficult..
Table looks like:

Sales No cust Count Amt
GA 576 6 ($4,010.58)
GA 60 38 ($169,339.72)
GA 695 37 ($62,403.28)
GA 705 83 ($441,496.55)
GB 14 29 ($121,965.26)
GB 21 2 ($692.28)
GB 327 4 ($4,873.50)
GB 150 34 ($156,590.02)
GB 180 236 ($1,787,157.93)
GC 197 39 ($92,733.60)
GC 329 36 ($87,728.84)
GC 228 6 ($26,845.04)
GC 505 17 ($24,706.36)
GC 702 5 ($5,908.80)
GC 414 1 ($2,162.06)
GC 554 4 ($3,024.60)
GC 281 146 ($251,082.96)
GC 839 78 ($682,850.40)

I want to add a column called rank. Rank each sales no by amt. Is
this some kind of a aggregate function? (Dcount). I always struggle
with them. Thanks in advance - this will seem simple to most - I'm
sure.

 

[Guest]

Try this --

SELECT [Sales No], cust, Count, Amt, (SELECT COUNT(*)
FROM [Lotto] T1
WHERE T1.[Sales No] = T.[Sales No]
AND T1.Amt <= T.Amt) AS Rank
FROM [Lotto] AS T
ORDER BY [Sales No], Amt;

You do know the by enclosing your dollars in parenthesis it makes them a
negative number to Access.

 

[kingston via AccessMonster.com]

Do you mean you want to sort this data? If so, create a query with the
fields and change the sort order for each field (Ascending or Descending) as
required. You can swap the order of the fields to sort one field before
another. If you want to add a field called Rank and populate that field,
it's going to take some coding.