How to get row number in SQL

[Wilson Hew]

Hi,

Anyone knows how to get the row number in Microsoft Access 2000?

In ORACLE, we can do like this:

SELECT ROWNUM, Field1, Field2, Field3 FROM Table1;

How do I write SQL statement in MS Access 2000? Thanks.

Wilson

 

[DJN]

What exactly are you trying to achieve? Have not used Oracle, so have no idea what RowNum does.

 

[Hayley Baxter]

Hi

This sample might be of some help to you. If you import your tables into this example you can search the fields and criteria you want.

 

[Wilson Hew]

Hi Hayley,

This attachment is not help me much! What I need to generate the row number from the SQL. For instance,

Table1
=====
Field1
Field2
Field3

Say, I want to list out all the fields with the record sequence number (row number) in Oracle, I can do like this:

SELECT RowNum, Field1, Field2, Field3 FROM Table1;

The RowNum is not exist in the Table1's field. How do I implement in Access 2000?

Thanks.

Wilson

 

[Jon K]

If you want to have a field in the query results containing number 1 for the first record, 2 for the second, 3 for the third etc, in Access the answer is: No, it's not possible.

However, you can use a Make-table query to create a table, and then add an autonumber field to the table in table design.