by VB function:
Dim m_RowNr(3) as Variant
'
Function RowNr(ByVal strQName As String, ByVal vUniqValue) As Long
' m_RowNr(3)
' 0 - Nr
' 1 - Query Name
' 2 - last date_time
' 3 - UniqValue
If Not m_RowNr(1) = strQName Then
m_RowNr(0) = 1
m_RowNr(1) = strQName
ElseIf DateDiff("s", m_RowNr(2), Now) > 9 Then
m_RowNr(0) = 1
ElseIf Not m_RowNr(3) = vUniqValue Then
m_RowNr(0) = m_RowNr(0) + 1
End If
m_RowNr(2) = Now
m_RowNr(3) = vUniqValue
RowNr = m_RowNr(0)
End Function
Usage(without sorting option):
SELECT RowNr('title_of_query_or_any_unique_text',A.id) as Nr,A.*
From table A
Order By A.id
if sorting required or multiple tables join then create intermediate table:
SELECT RowNr('title_of_query_or_any_unique_text',A.id) as Nr,A.*
INTO table_with_Nr
From table A
Order By A.id