How to add sequenced number based on sorted value in query in Access












0















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!










share|improve this question




















  • 2





    Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL

    – Darren Bartrup-Cook
    Nov 19 '18 at 10:31
















0















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!










share|improve this question




















  • 2





    Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL

    – Darren Bartrup-Cook
    Nov 19 '18 at 10:31














0












0








0








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!










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 19 '18 at 10:04









a_horse_with_no_name

298k46452548




298k46452548










asked Nov 19 '18 at 9:49









MarioMario

195215




195215








  • 2





    Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL

    – Darren Bartrup-Cook
    Nov 19 '18 at 10:31














  • 2





    Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL

    – Darren Bartrup-Cook
    Nov 19 '18 at 10:31








2




2





Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL

– Darren Bartrup-Cook
Nov 19 '18 at 10:31





Possible duplicate of How to show row number in Access query like ROW_NUMBER in SQL

– Darren Bartrup-Cook
Nov 19 '18 at 10:31












2 Answers
2






active

oldest

votes


















2














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.






share|improve this answer





















  • 1





    Thank you. That did the trick!

    – Mario
    Nov 19 '18 at 12:13



















2














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






share|improve this answer


























  • Thank you very much for your help but Lee Mac solution seems simpler and it works.

    – Mario
    Nov 19 '18 at 12:15











  • 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
    Nov 19 '18 at 12:24











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















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

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














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.






share|improve this answer





















  • 1





    Thank you. That did the trick!

    – Mario
    Nov 19 '18 at 12:13
















2














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.






share|improve this answer





















  • 1





    Thank you. That did the trick!

    – Mario
    Nov 19 '18 at 12:13














2












2








2







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.






share|improve this answer















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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 19 '18 at 12:24

























answered Nov 19 '18 at 11:13









Lee MacLee Mac

4,33731541




4,33731541








  • 1





    Thank you. That did the trick!

    – Mario
    Nov 19 '18 at 12:13














  • 1





    Thank you. That did the trick!

    – Mario
    Nov 19 '18 at 12:13








1




1





Thank you. That did the trick!

– Mario
Nov 19 '18 at 12:13





Thank you. That did the trick!

– Mario
Nov 19 '18 at 12:13













2














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






share|improve this answer


























  • Thank you very much for your help but Lee Mac solution seems simpler and it works.

    – Mario
    Nov 19 '18 at 12:15











  • 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
    Nov 19 '18 at 12:24
















2














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






share|improve this answer


























  • Thank you very much for your help but Lee Mac solution seems simpler and it works.

    – Mario
    Nov 19 '18 at 12:15











  • 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
    Nov 19 '18 at 12:24














2












2








2







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






share|improve this answer















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







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 19 '18 at 12:22

























answered Nov 19 '18 at 10:34









GustavGustav

29.9k51835




29.9k51835













  • Thank you very much for your help but Lee Mac solution seems simpler and it works.

    – Mario
    Nov 19 '18 at 12:15











  • 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
    Nov 19 '18 at 12:24



















  • Thank you very much for your help but Lee Mac solution seems simpler and it works.

    – Mario
    Nov 19 '18 at 12:15











  • 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
    Nov 19 '18 at 12:24

















Thank you very much for your help but Lee Mac solution seems simpler and it works.

– Mario
Nov 19 '18 at 12:15





Thank you very much for your help but Lee Mac solution seems simpler and it works.

– Mario
Nov 19 '18 at 12:15













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
Nov 19 '18 at 12:24





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
Nov 19 '18 at 12:24


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














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





















































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







這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()