Row numbers in query result using Microsoft Access

Question

I always use this query in sql server to get Row number in a table   SELECT   FROM    SELECT                   Row number                    OVER                     ORDER BY  myidentitycolumn   RowID         FROM   mytable  sub WHERE  rowid   15     Now I am working in Access 2010 and this seems to be not working   Is there any replacement for this query in Access

User · Answer

Though this is an old question  this has worked for me  but I ve never tested its efficiency     SELECT       SELECT COUNT t1 SourceID        FROM  SourceTable  t1       WHERE t1 SourceID lt t2 SourceID  AS RowID       t2 field2       t2 field3       t2 field4       t2 field5 FROM      SourceTable AS t2 ORDER BY      t2 SourceID    Some advantages of this method    It doesn t rely on the order of the table  either - the RowID is calculated on its actual value and those that are less than it  This method can be applied to any  primary key  type  e g  Number  String or Date   This method is fairly SQL agnostic  or requires very little adaptation    Final Thoughts  Though this will work with practically any data type  I must emphasise that  for some  it may create other problems   For instance  with strings  consider     ID     Description    ROWID aaa    Aardvark           1 bbb    Bear               2 ccc    Canary             3   If I were to insert  bba  Boar  then the Canary RowID will change       ID     Description    ROWID aaa    Aardvark           1 bbb    Bear               2 bba    Boar               3 ccc    Canary             4

User · Answer

Another way to assign a row number in a query is to use the DCount function   SELECT    DCount   ID     mytable     ID  lt     amp   ID   AS row id FROM  mytable  WHERE row id 15

User · Answer

MS-Access doesn t support ROW NUMBER     Use TOP 1   SELECT TOP 1   FROM  MyTable  ORDER BY  MyIdentityCOlumn    If you need the 15th row - MS-Access has no simple  built-in  way to do this   You can simulate the rownumber by using reverse nested ordering to get this   SELECT TOP 1   FROM     SELECT TOP 15     FROM  MyTable    ORDER BY  MyIdentityColumn    t ORDER BY  MyIdentityColumn  DESC

User · Answer

Since I am sorting alphabetically on a string field and NOT by ID  the Count    and DCOUNT    approaches didn t work for me   My solution was to write a function that returns the Row Number   Option Compare Database Option Explicit Private Rst As Recordset  Public Function GetRowNum ID As Long  As Long   If Rst Is Nothing Then     Set Rst   CurrentDb OpenRecordset  SELECT ID FROM FileList ORDER BY RealName     End If   Rst FindFirst  ID    amp  ID   GetRowNum   Rst AbsolutePosition   1   Release the Rst 1 sec after it s last use  ------------------------------------------   SetTimer Application hWndAccessApp  1  1000  AddressOf ReleaseRst   End Function   Private Sub ReleaseRst ByVal hWnd As LongPtr  ByVal uMsg As Long  ByVal nIDEEvent As Long  ByVal dwTime As Long    KillTimer Application hWndAccessApp  1    Set Rst   Nothing End Sub

User · Answer

I needed the best x results of points per team   Ranking does not solves this problem when there are results with equal points  So I need a recordnumber  I made a VBA function in Access to create a recordnumber that resets on ID change   You have to query this query with where recordnumber  lt   x to get the points per team   NB Access changes the record-number   when you query the query filtered on record number when you filter out some results when you change the sort order   That is not what I thought that would happen   Solved this by using a temporary table and saving the recordnumbers and keys or an extra field in the table   SELECT ID  Points  RecordNumberOffId  ID   AS Recordnumber FROM Team ORDER BY ID ASC  Points DESC    It uses 3 module level variables to remember between calls  Dim PreviousID As Long Dim PreviousRecordNumber As Long Dim TimeLastID As Date  Public Function RecordNumberOffID ID As Long  As Long   ID is sortgroup identity  Reset if last call longer dan nn seconds in the past If Time   - TimeLastID  gt  0 0003 Then  0 000277778   1 second     PreviousID   0     PreviousRecordNumber   0 End If If ID  lt  gt  PreviousID Then     PreviousRecordNumber   0     PreviousID   ID End If PreviousRecordNumber   PreviousRecordNumber   1 RecordNumberOffID   PreviousRecordNumber TimeLastID   Time   End Function

User · Answer

I might be late  Simply add a new field ID in the table with type AutoNumber   This will generate unique IDs and can utilize in Access too

User · Answer

Thanks  for your solutions above  DCount did the trick for me too   I had to use a combination of date columns and a unique identifier for the sorting portion of it  as well as some additional conditions   so here is what I ended up doing   1  I had to check if DateColumnA was null  then check if DateColumnB was null  then use DateColumnC  then  if multiple records have the same date value  they all end up with the same id    2  So  I figured I would use the integer unique ID of the table  and add it up to the time as  minutes   This will always provide different results  3  Finally  the logic above results in the count starting in 0    so just add 1   SELECT   1 DCount   RequestID     Request    Archived 0 and ProjectPhase  lt  gt  2 and iif isnull DateColumnA  true iif isnull DateColumnB  true DateColumnC DateColumnB  DateColumnA   RequestID 3600   lt      amp  if isnull DateColumnA  true iif isnull DateColumnB  true DateColumnC DateColumnB  DateColumnA     RequestID 3600   amp       AS RowID   FROM   Request ORDER BY 1   I hope this helps you out

[sql] Row numbers in query result using Microsoft Access

Examples related to sql

Examples related to ms-access