Ranking in Query

[jhicsupt]

I need to rank customers by TotalAll. There are ten customers, I need to put
1, 2, 3, 4, 5...10 in a column.

SELECT [IP-TopTenRanking1].CustNo, [IP-TopTenRanking1].TotalAll
FROM [IP-TopTenRanking1]
GROUP BY [IP-TopTenRanking1].CustNo, [IP-TopTenRanking1].TotalAll;

Thanks in advance

 

[Tom van Stiphout]

On Wed, 17 Dec 2008 01:46:01 -0800, jhicsupt

This numbering is rarely ever needed. Just put an Order By clause in
your SQL statement.
If you really must: in a Report you can put an unbound field with "=1"
and Running Sum = True.

-Tom.
Microsoft Access MVP

 

[jhicsupt]

I need to put a ranking number in because I will use this query with another
query and I need to group the highest dollar amounts (totalall) in descending
order. The other query will have CustNo grouping. My grouping would be
Total $ amount by CustID. CustID could have multiple entries, so if ABC
Company has 5 entries but is the second highest $ amount, I need that to
display 2nd.

Hopefully this isn't too confusing.

Thanks.

On Wed, 17 Dec 2008 01:46:01 -0800, jhicsupt

This numbering is rarely ever needed. Just put an Order By clause in
your SQL statement.
If you really must: in a Report you can put an unbound field with "=1"
and Running Sum = True.

-Tom.
Microsoft Access MVP

I need to rank customers by TotalAll. There are ten customers, I need to put
1, 2, 3, 4, 5...10 in a column.

SELECT [IP-TopTenRanking1].CustNo, [IP-TopTenRanking1].TotalAll
FROM [IP-TopTenRanking1]
GROUP BY [IP-TopTenRanking1].CustNo, [IP-TopTenRanking1].TotalAll;

Thanks in advance

 

[Jellifish]

http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html