Rank and row number in Access?

[Steven Cheng]

I am trying to general a query where by their is ranking within groups like
the table below:

firm-id rank
1
1
1
2
2
3

want:

firm-id rank
1 1
1 2
1 3
2 1
2 2
3 1
DP file

are row number and rank not valid functions in the ADP file?

 

[John W. Vinson]

I am trying to general a query where by their is ranking within groups like
the table below:

firm-id rank
1
1
1
2
2
3

want:

firm-id rank
1 1
1 2
1 3
2 1
2 2
3 1
DP file

are row number and rank not valid functions in the ADP file?

No, they are not. Relational tables should be viewed as unordered "bags" of
records; there is no builtin "row number", and records will be retrieved in
any order that the program finds convenient, unless you have an ORDER BY
clause which determines that order. What other fields are available? Is there
anything within the record that would distinguish the third-rank entry for
firm 1 from the first-rank entry for that firm?

 

[Dorian]

What is the purpose of the Rank column and how are the ranks to be assigned
to like ids?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[vanderghast]

If you can connect to an MS SQL Server 2005 or later, then you can quite
probably use RANK or Row_Number( ) functions introduced in 2005. The syntax
is:

--------------
SELECT *, ROW_NUMBER( ) OVER ( ORDER BY firmId ) AS rowNumber
FROM table
--------------

as example. That does not work with Jet, only with MS SQL Server, and only
if it is 2005 or later (2008).

You cannot access the field rowNumber in a where clause of the same query
where it is defined, though (since the SELECT clause is evaluated AFTER the
WHERE clause), but you can use a virtual table to reach it:

---------------
SELECT *

FROM (
SELECT *, ROW_NUMBER( ) OVER ( ORDER BY frmID) AS rowNumber
FROM table ) AS x

WHERE rowNumber BETWEEN 10 and 20