# Add RowNumber Problem (1 Viewer)

#### naobao

##### Registered User.
I used the following SQL to automatically generate the RowNumber column

SELECT A.id, (SELECT COUNT(*) FROM packing_ship_mark_1a WHERE A.ID>=ID) AS RowNum
FROM packing_ship_mark_1a AS A;

id RowNumber
596 24
568 1
571 2
572 3
573 4
574 5
575 6
576 7
577 8
578 9
579 10
597 25
581 11
582 12
585 13
586 14
587 15
588 16
589 17
590 18
591 19
592 20
593 21
594 22
595 23

But the RowNumber is not displayed as I expected. How can I achieve the following result?

596 1
568 2
571 3
572 4
573 5
574 6
575 7
576 8
577 9
578 10
579 11
597 12
582 13
581 14
585 15
587 16
588 17
589 18
590 19
591 20
592 21
593 22
594 23
595 24
586 25

I need equal "596" to "1"

sort to your query By ID
Functionally, this already happens with the following expression: WHERE A.ID>=ID

@naobao
For the method used, you need a field whose sorted contents reflect the exact order required. The ID field alone does not provide this. Are there any other fields in the table, perhaps a usable one?

If you have SQL Server as backend: T-SQL has a function called Rownumber.
Otherwise, I can think of three ways you can create the numbering you want. However, all of them go beyond the scope of a simple query.

1) Create a field in the table later:
SQL:
``ALTER TABLE packing_ship_mark_1a ADD COLUMN RowNumber Counter(1,1)``

2) The field already exists in the table. The table is loaded into a recordset, which fixes the order of the records. The value for the field is then incremented using a simple loop.

3) The QueryIncrement function generates a numbering. However, this is not stable when the query is used again. Therefore, the generated query result must be fixed, in this case using a maketable query.
SQL:
``````SELECT
ID,
QueryIncrement(ID, 1) AS RowNumber
INTO
NewTable
FROM
packing_ship_mark_1a
WHERE
Reset_Globals() = True``````
Code:
``````' mod_QueryIncrement

Public gQI_Category As String
Public gQI_Icount As Long

Public Function QueryIncrement(ByVal Init As Variant, ByVal StartValue As Long, ParamArray Categories() As Variant) As Long
Dim k As Long, v As Variant
Dim sAllCategories As String

v = Init
If IsMissing(Categories) Then
sAllCategories = "\$\$\$\$\$"
Else
For k = 0 To UBound(Categories)
If IsNull(Categories(k)) Then Categories(k) = vbNullString
Next
sAllCategories = Join(Categories, "|")
End If

If gQI_Category = sAllCategories Then
gQI_Icount = gQI_Icount + 1
Else
gQI_Category = sAllCategories
gQI_Icount = StartValue
End If
QueryIncrement = gQI_Icount
End Function

Public Function Reset_Globals() As Boolean
gQI_Category = vbNullString
gQI_Icount = 0
Reset_Globals = True
End Function``````

Functionally, this already happens with the following expression: WHERE A.ID>=ID

@naobao
For the method used, you need a field whose sorted contents reflect the exact order required. The ID field alone does not provide this. Are there any other fields in the table, perhaps a usable one?

If you have SQL Server as backend: T-SQL has a function called Rownumber.
Otherwise, I can think of three ways you can create the numbering you want. However, all of them go beyond the scope of a simple query.

1) Create a field in the table later:
SQL:
``ALTER TABLE packing_ship_mark_1a ADD COLUMN RowNumber Counter(1,1)``

2) The field already exists in the table. The table is loaded into a recordset, which fixes the order of the records. The value for the field is then incremented using a simple loop.

3) The QueryIncrement function generates a numbering. However, this is not stable when the query is used again. Therefore, the generated query result must be fixed, in this case using a maketable query.
SQL:
``````SELECT
ID,
QueryIncrement(ID, 1) AS RowNumber
INTO
NewTable
FROM
packing_ship_mark_1a
WHERE
Reset_Globals() = True``````
Code:
``````' mod_QueryIncrement

Public gQI_Category As String
Public gQI_Icount As Long

Public Function QueryIncrement(ByVal Init As Variant, ByVal StartValue As Long, ParamArray Categories() As Variant) As Long
Dim k As Long, v As Variant
Dim sAllCategories As String

v = Init
If IsMissing(Categories) Then
sAllCategories = "\$\$\$\$\$"
Else
For k = 0 To UBound(Categories)
If IsNull(Categories(k)) Then Categories(k) = vbNullString
Next
sAllCategories = Join(Categories, "|")
End If

If gQI_Category = sAllCategories Then
gQI_Icount = gQI_Icount + 1
Else
gQI_Category = sAllCategories
gQI_Icount = StartValue
End If
QueryIncrement = gQI_Icount
End Function

Public Function Reset_Globals() As Boolean
gQI_Category = vbNullString
gQI_Icount = 0
Reset_Globals = True
End Function``````
tabel is on sql server

How can I achieve the following result?

596 1
568 2
571 3
Is the first row a typo?

tabel is on sql server
Create a view in SQL Server and link it as a table in your frontend.

you can also do this in access using a UDF.
put this in a Module and replace "dbo_table_1" with the name of your linked table to mssql:
Code:
``````Public Function fnRank1(ByVal id As Long) As Long
'
' put the Linked table name here and replace dbo_table_1
'
Const the_linked_table As String = "packing_ship_mark_1a"

Static d_obj As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long

' note to reset this function pass 0 or negative value to the function
If id < 1 Then
Set d_obj = Nothing
Exit Function
End If

If (d_obj Is Nothing) Then

Set d_obj = CreateObject("scripting.dictionary")
Set db = CurrentDb

With rs
If Not (.BOF And .EOF) Then
rs.MoveLast
rs.MoveFirst
End If
Do Until .EOF
i = i + 1
d_obj(!id & "") = i
.MoveNext
Loop
.Close
End With

End If

fnRank1 = d_obj(id & "")

End Function``````

you then change your query to:
Code:
``````SELECT A.id,  fnRank1(A.[id]) AS RowNum
FROM packing_ship_mark_1a AS A;``````

//Edit: i already replaced the table on the code