How to add sequenced number based on sorted value in query in Access
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
add a comment |
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
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
add a comment |
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
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 Nov 19 '18 at 10:04
a_horse_with_no_name
294k46449541
294k46449541
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
add a comment |
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
add a comment |
2 Answers
2
active
oldest
votes
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.
1
Thank you. That did the trick!
– Mario
Nov 19 '18 at 12:13
add a comment |
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
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
add a comment |
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
});
}
});
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
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
1
Thank you. That did the trick!
– Mario
Nov 19 '18 at 12:13
add a comment |
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.
1
Thank you. That did the trick!
– Mario
Nov 19 '18 at 12:13
add a comment |
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.
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.
edited Nov 19 '18 at 12:24
answered Nov 19 '18 at 11:13
Lee MacLee Mac
3,79131339
3,79131339
1
Thank you. That did the trick!
– Mario
Nov 19 '18 at 12:13
add a comment |
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
add a comment |
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
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
add a comment |
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
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
add a comment |
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
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
edited Nov 19 '18 at 12:22
answered Nov 19 '18 at 10:34
GustavGustav
29.6k51734
29.6k51734
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
add a comment |
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
add a comment |
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.
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
Nov 19 '18 at 10:31