Ranking in Query

J

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
 
T

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
 
J

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.
Tom van Stiphout said:
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
Click to expand...
Click to expand...
 
Top