Re: RowNum Equivalent in MS Access

B

Brian Camire

There is no ROWNUM equivalent in Access.

However, you can do something similar using a self-join, an ORDER BY clause,
and one or more fields by which you can rank and uniquely identify each
record. For example, if you had one such field named "ID" you could add a
"ROWNUM" field by creating a new query based on your original query whose
SQL looked something like this:

SELECT
[Your Original Query].*,
(SELECT
COUNT(*)
FROM
[Your Original Query] AS [Self]
WHERE
[Self].[ID] <= [Your Original Query].[ID]) AS [ROWNUM]
FROM
[Your Original Query]
ORDER BY
[Your Original Query].[ID]

The results of this query are not updateable. If you need the query to be
updateable, you might use the DCount function instead of a subquery. If you
do this, though, remember that (unlike in Oracle) the value of the "ROWNUM"
can change while results of the query are open.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top