query to show result with the row no(like serial no)

Question

Example Data:

Col1     Col2     Col3
  a        1        x
  a        5        y
  c        8        w 
  d        9        v

Result must be

Col1     Col2     Col3    Slno
  a        1        x       1
  a        5        y       2
  c        8        w       3
  d        9        v       4

Answer 1

You can use this query, but col1 has to be unique field

SELECT (select sum(1) from tab1 t where t.col1<=t1.col1 ) AS slno, t1.col1, t1.col2, t1.col3  FROM tab1 AS t1;

For not unique fields you can use this

1)this code in module

Private curNum As Long

Public Function startNum() As Boolean
  curNum = 0
  startNum = True
End Function

Public Function GetNextNum(anyField) As Long
  curNum = curNum + 1
  GetNextNum = curNum
End Function

2)this code in query

SELECT DISTINCT *, GetNextNum([AnyField]) AS MyCounter
FROM MyTable
WHERE startNum()=True;