Re: RowNum Equivalent in MS Access

  • Thread starter Thread starter Brian Camire
  • Start date Start date
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.
 
Back
Top