Mixing
How to add sequenced number based on sorted value in query in Access
up vote
0
down vote
favorite
Can somebody please give me the solution or explain the right approach for this question. So, I have a query which returns some values (att1). I would like also to have next to it the values which would represent a sorted order of att1. Something like this
att1 att2
19 3
2 2
46 4
78 5
1 1
Thanks in advanced!
sql ms-access
edited yesterday
a_horse_with_no_name
286k45429526
asked yesterday
Mario
185215
add a comment |
up vote
0
down vote
favorite
Can somebody please give me the solution or explain the right approach for this question. So, I have a query which returns some values (att1). I would like also to have next to it the values which would represent a sorted order of att1. Something like this
att1 att2
19 3
2 2
46 4
78 5
1 1
Thanks in advanced!
sql ms-access
edited yesterday
a_horse_with_no_name
286k45429526
asked yesterday
Mario
185215
2
Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL
– Darren Bartrup-Cook
yesterday
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Can somebody please give me the solution or explain the right approach for this question. So, I have a query which returns some values (att1). I would like also to have next to it the values which would represent a sorted order of att1. Something like this
att1 att2
19 3
2 2
46 4
78 5
1 1
Thanks in advanced!
sql ms-access
edited yesterday
a_horse_with_no_name
286k45429526
asked yesterday
Mario
185215
Can somebody please give me the solution or explain the right approach for this question. So, I have a query which returns some values (att1). I would like also to have next to it the values which would represent a sorted order of att1. Something like this
att1 att2
19 3
2 2
46 4
78 5
1 1
Thanks in advanced!
sql ms-access
sql ms-access
edited yesterday
a_horse_with_no_name
286k45429526
asked yesterday
Mario
185215
edited yesterday
a_horse_with_no_name
286k45429526
asked yesterday
Mario
185215
edited yesterday
a_horse_with_no_name
286k45429526
edited yesterday
a_horse_with_no_name
286k45429526
edited yesterday
a_horse_with_no_name
286k45429526
286k45429526
asked yesterday
Mario
185215
asked yesterday
Mario
185215
asked yesterday
Mario
185215
185215
2
Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL
– Darren Bartrup-Cook
yesterday
add a comment |
2
Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL
– Darren Bartrup-Cook
yesterday
2
2
Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL
– Darren Bartrup-Cook
yesterday
Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL
– Darren Bartrup-Cook
yesterday
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
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.
answered 23 hours ago
Lee Mac
2,76221036
1
Thank you. That did the trick!
– Mario
22 hours ago
add a comment |
up vote
2
down vote
I wrote an article on the various methods for this:
Sequential Rows in Microsoft Access
In its simplest form:
SELECT RowNumber(CStr([ID])) AS RowID, *, FROM SomeTable;
using the RowNumber function:
' 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.
'
' 2018-08-23. 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
All code is also on GitHub: VBA.RowNumbers
answered yesterday
Gustav
28.7k51734
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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.
answered 23 hours ago
Lee Mac
2,76221036
1
Thank you. That did the trick!
– Mario
22 hours ago
add a comment |
up vote
2
down vote
accepted
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.
answered 23 hours ago
Lee Mac
2,76221036
1
Thank you. That did the trick!
– Mario
22 hours ago
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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.
answered 23 hours ago
Lee Mac
2,76221036
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.
answered 23 hours ago
Lee Mac
2,76221036
edited 22 hours ago
answered 23 hours ago
Lee Mac
2,76221036
answered 23 hours ago
Lee Mac
2,76221036
answered 23 hours ago
Lee Mac
2,76221036
2,76221036
1
Thank you. That did the trick!
– Mario
22 hours ago
add a comment |
1
Thank you. That did the trick!
– Mario
22 hours ago
1
1
Thank you. That did the trick!
– Mario
22 hours ago
Thank you. That did the trick!
– Mario
22 hours ago
add a comment |
up vote
2
down vote
I wrote an article on the various methods for this:
Sequential Rows in Microsoft Access
In its simplest form:
SELECT RowNumber(CStr([ID])) AS RowID, *, FROM SomeTable;
using the RowNumber function:
' 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.
'
' 2018-08-23. 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
All code is also on GitHub: VBA.RowNumbers
answered yesterday
Gustav
28.7k51734
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
add a comment |
up vote
2
down vote
I wrote an article on the various methods for this:
Sequential Rows in Microsoft Access
In its simplest form:
SELECT RowNumber(CStr([ID])) AS RowID, *, FROM SomeTable;
using the RowNumber function:
' 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.
'
' 2018-08-23. 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
All code is also on GitHub: VBA.RowNumbers
answered yesterday
Gustav
28.7k51734
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
add a comment |
up vote
2
down vote
up vote
2
down vote
I wrote an article on the various methods for this:
Sequential Rows in Microsoft Access
In its simplest form:
SELECT RowNumber(CStr([ID])) AS RowID, *, FROM SomeTable;
using the RowNumber function:
' 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.
'
' 2018-08-23. 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
All code is also on GitHub: VBA.RowNumbers
answered yesterday
Gustav
28.7k51734
I wrote an article on the various methods for this:
Sequential Rows in Microsoft Access
In its simplest form:
SELECT RowNumber(CStr([ID])) AS RowID, *, FROM SomeTable;
using the RowNumber function:
' 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.
'
' 2018-08-23. 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
All code is also on GitHub: VBA.RowNumbers
answered yesterday
Gustav
28.7k51734
edited 22 hours ago
answered yesterday
Gustav
28.7k51734
answered yesterday
Gustav
28.7k51734
answered yesterday
Gustav
28.7k51734
28.7k51734
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
add a comment |
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Thank you very much for your help but Lee Mac solution seems simpler and it works.
– Mario
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
Yes, it works for few records, but if you have many, it will be very slow. And the required SQL is as simple as it can get: One line. See edited answer, please.
– Gustav
22 hours ago
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371994%2fhow-to-add-sequenced-number-based-on-sorted-value-in-query-in-access%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL
– Darren Bartrup-Cook
yesterday