Solved

How to get rownum in Access table

Posted on 2002-04-27
6
16,718 Views
Last Modified: 2009-11-23
In Oracle, i can retrieve the row number of selected records by using rownum.

Example:
Select rownum, id, name from MyTable;

But how can i retrieve the row number of a record in Ms-Access (Equivalent to rownum in Oracle)??

Thanks
0
Comment
Question by:siiking
  • 3
  • 2
6 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6974478
Hi Siiking,

Since access doesn't do row nums, you can try this approach

http://groups.google.com/groups?hl=nl&selm=zmiJ6.30756%24_f3.516398%40news20.bellglobal.com&rnum=5

as stated in that post if you don't want to go the SQL road then there are two other ways to go

-do it with an ADO recordset and some VB
-write your own function also in VB

HTH:O)Bruintje
0
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
ID: 6974485
a little addition, in a real relational database there are no row numbers because the records are created on a heap.

and i'm speculating here that Oracle also uses a function to order the records prior to returning the row numbers. this is what you're going to do in Access also through a SQL query or with your own function. the ADO recordset will return pages of records and you bring in a order in the resultset.

the fastest way to solve this is inserting a column with a num as field that's sequential, but for every deletion or insertion you've to rerow the column to adjust to the new order. problem with this approach is that a record can be number 4 in a query and number 3 in one three months later

so it all depends on what you need to do.

:O)Bruintje
0
 
LVL 15

Expert Comment

by:cquinn
ID: 6975304
Just add an autonumber column - you may get gaps though, as stated by bruintje, when you delete records
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57
ID: 6980373
If your talking about a DAO recordset, it's the AbsolutePosition Property.  If not, fill in some more details.

Jim.
0
 
LVL 57
ID: 6980378
Note that if you want a simple row number added to a query, that's easily done and you don't need an autonumber.

As I said, fill in some more details on what it is your doing.  There may be other options as well.

Jim.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6991603
that's my second C for the day, if you don't like the comment don't accept it as answer especially when other experts are commenting also and we get no feedback fom your side.......darn!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question