Returning row number on MS Access

Question

I have 4 tables, from which i select data with help of joins in select query...I want a serial no.(row number) per record as they are fetched. first fetched record should be 1, next 2 and so on...

In oracle the equiavelent in RowNum.

Answer 1

With the following table

SET NOCOUNT ON 

CREATE TABLE people 
( 
    firstName VARCHAR(32), 
    lastName VARCHAR(32) 
) 
GO 

INSERT people VALUES('Aaron', 'Bertrand') 
INSERT people VALUES('Andy', 'Roddick') 
INSERT people VALUES('Steve', 'Yzerman') 
INSERT people VALUES('Steve', 'Vai') 
INSERT people VALUES('Joe', 'Schmoe')

You can use a sub query to create the counting row:

SELECT 
    rank = COUNT(*), 
    a.firstName, 
    a.lastName 
FROM 
    people a  
    INNER JOIN people b 
    ON  
        a.lastname > b.lastname 
        OR 
        ( 
            a.lastName = b.lastName 
            AND 
            a.firstName >= b.firstName 
        ) 
GROUP BY 
    a.firstName, 
    a.lastName 
ORDER BY 
    rank

The problem with this method is that the count will be off if there are duplicates in your results set.

This article explains pretty well how to add a row counting column to your query.

Answer 2

The answer by Brettski is ASP flavored and would need a lot of editing.

SELECT DCOUNT("YourField","YourTable","YourField <= '" & [counter] & "'") AS RowNumber,  
YourField as counter FROM YourTable;

Above is your basic syntax. You are likely to find this runs very slow. My typical solution is a bucket table with Autonumber field. That seems very kludgy to many people, but it gives me control and probably in this case it allows speed.