Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17383
  • Last Modified:

How to get rownum in Access table

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
siiking
Asked:
siiking
  • 3
  • 2
1 Solution
 
bruintjeCommented:
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
 
bruintjeCommented:
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
 
cquinnCommented:
Just add an autonumber column - you may get gaps though, as stated by bruintje, when you delete records
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
If your talking about a DAO recordset, it's the AbsolutePosition Property.  If not, fill in some more details.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
bruintjeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now