Row numbers in query result using Microsoft Access
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
How to show row number in Access query like ROW_NUMBER in SQL
You can try this query:
Select A.*, (select count(*) from Table1 where A.ID>=ID) as RowNo
from Table1 as A
order by A.ID
Add a row number to a microsoft access query
One way to do it is with a correlated subquery:
SELECT s.pluquo,
(SELECT COUNT(*)
FROM (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS t
WHERE t.pluquo <= s.pluquo
) AS row_number
FROM (
SELECT DISTINCT pluquo
FROM SYNC002_ACCESS
) AS s
Or with a self join:
SELECT s1.pluquo, COUNT(*) AS row_number
FROM (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS s1
INNER JOIN (SELECT DISTINCT pluquo FROM SYNC002_ACCESS) AS s2
ON s2.pluquo <= s1.pluquo
GROUP BY s1.pluquo
Adding a Row Number in Query
One way to do this is to use the count
function in a subquery. Not sure it scales well though and there are probably better ways...
select
(select count(*) from city where ID_city <= t1.ID_city) as row_number,
*
from city t1
How to add sequenced number based on sorted value in query in Access
Assuming a table name of table1
, The following should yield the desired result:
select a.att1, (select count(*) from table1 b where b.att1 <= a.att1) as att2
from table1 a;
For every record, the query calculates the number of records less than or equal to the current record, which is then output as the sort index.
Running Dlookup() on a query with row count column as criteria (MS access)
I don't have your data, so can't run a full test, but I ran something similar using my RowNumber
function (below), and DLookup
was able to filter on the returned rownumber (your [NO]) and return a value from another field.
So, try to use this function (your SQL will need minor adjustments, see in-line comments, please):
' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query having an ID with an index):
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' Usage (typical select query having an ID without an index):
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber("","",True)=0);
'
' Usage (with group key):
' SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
' Call RowNumber(vbNullString, True)
' 2. Run query:
' INSERT INTO TempTable ( [RowID] )
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable;
'
' Usage (typical append query, automatic reset):
' INSERT INTO TempTable ( [RowID] )
' SELECT RowNumber(CStr([ID])) AS RowID, *
' FROM SomeTable
' WHERE (RowNumber("","",True)=0);
'
' 2020-05-29. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
ByVal Key As String, _
Optional ByVal GroupKey As String, _
Optional ByVal Reset As Boolean) _
As Long
' Uncommon character string to assemble GroupKey and Key as a compound key.
Const KeySeparator As String = "¤§¤"
' Expected error codes to accept.
Const CannotAddKey As Long = 457
Const CannotRemoveKey As Long = 5
Static Keys As New Collection
Static GroupKeys As New Collection
Dim Count As Long
Dim CompoundKey As String
On Error GoTo Err_RowNumber
If Reset = True Then
' Erase the collection of keys and group key counts.
Set Keys = Nothing
Set GroupKeys = Nothing
Else
' Create a compound key to uniquely identify GroupKey and its Key.
' Note: If GroupKey is not used, only one element will be added.
CompoundKey = GroupKey & KeySeparator & Key
Count = Keys(CompoundKey)
If Count = 0 Then
' This record has not been enumerated.
'
' Will either fail if the group key is new, leaving Count as zero,
' or retrieve the count of already enumerated records with this group key.
Count = GroupKeys(GroupKey) + 1
If Count > 0 Then
' The group key has been recorded.
' Remove it to allow it to be recreated holding the new count.
GroupKeys.Remove (GroupKey)
Else
' This record is the first having this group key.
' Thus, the count is 1.
Count = 1
End If
' (Re)create the group key item with the value of the count of keys.
GroupKeys.Add Count, GroupKey
End If
' Add the key and its enumeration.
' This will be:
' Using no group key: Relative to the full recordset.
' Using a group key: Relative to the group key.
' Will fail if the key already has been created.
Keys.Add Count, CompoundKey
End If
' Return the key value as this is the row counter.
RowNumber = Count
Exit_RowNumber:
Exit Function
Err_RowNumber:
Select Case Err
Case CannotAddKey
' Key is present, thus cannot be added again.
Resume Next
Case CannotRemoveKey
' GroupKey is not present, thus cannot be removed.
Resume Next
Case Else
' Some other error. Ignore.
Resume Exit_RowNumber
End Select
End Function
The full story can be found in my project VBA.RowNumbers.
Related Topics
Sql Alias Column Name Needed in Case When Statement
Executing SQL Scripts on Docker Container
How to Extract Year from Hire_Date in My SQL
Difference Between "INNER JOIN" and "OUTER JOIN"
How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database
Replace Null Value With Previous Available Value in Row SQL Server Query
How to Get Previous Row Data in SQL Server
Select Different Values from Same Column in a Table and Display It Under Different Columns
How to Store the Select Statement Output into Variable Thro Psql ( Postgresql )
How to Get Calendar Quarter from a Date in Tsql
Unable to Get Min and Max Time from a Datetime Column from Every Backday
Calculating Percent Change Between Two Rows
How to Insert an Image in Sqlite Database(Table)
How to Select the Top 3 Salaries of the Department
Oracle SQL Get the First and Last Records from an Ordered Dataset
Select Count of Total Products as Well as Out of Stock Products from Table
Extract String Part Between Single Quotes from Formula'S Divisor
Count Table Difference Between Two Tables by a Complex Key in Hive