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?

On Mon, 12 Apr 2010 16:52:01 -0700, Steven Cheng
<StevenCheng@discussions.microsoft.com> wrote:

>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?
-- 

             John W. Vinson [MVP]

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".


"Steven Cheng" wrote:

> 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?

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
---------------



Vanderghast, Access MVP



"Steven Cheng" <StevenCheng@discussions.microsoft.com> wrote in message 
news:609057D7-C7F4-41B0-A94C-527737F055FB@microsoft.com...
>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?