By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,122 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,122 IT Pros & Developers. It's quick & easy.

Sort Angst

100+
P: 124
I have over a thousand rows of data that needs to be sorted by a field called "Sort Order." I have assigned numbers to SortOrder based on the fields they wanted to sort on and it sorts fine that way. However, many of the records have duplicate AcctNum field values and now they want any duplicates to appear together, regardless of the aforementioned sort order. In other words, a record should be sorted by the sort order except they want to have duplicates appear sequentially after the first instance of the AcctNum. Can this be done? Thanks for any advice.
Mar 29 '17 #1

✓ answered by jforbes

See if this is more to your liking. It more closely resembles your data based on the code you provided.

Share this Question
Share on Google+
13 Replies


jforbes
Expert 100+
P: 1,107
Maybe, it would kind of depend on your data. What is in the SortOrder Field and how is it populated.
Mar 30 '17 #2

100+
P: 124
The sortorder field is a numeric field that I created and numbered sequentially based on the text criteria that they said they wanted the data sorted in. Now they are saying that they want duplicate acctnum records (of which there are many) to forego the sort order algorithm and place consecutively with other records of the same acctnum. Besides the dupes, they want to use the sortorder field sorting. I am at a total loss as to how to accomplish this despite being fairly experienced at SQL and VBA.
Mar 30 '17 #3

jforbes
Expert 100+
P: 1,107
I'm still unclear on how the SortOrder field is populated, but it sounds like there are some rules that are used to determine the SortOrder. Whatever routine that is used to populate the SortOrder column could be modified to either group by the AcctNum or to stamp the same SortOrder on all records with identical AcctNums.
Mar 30 '17 #4

100+
P: 124
I'm trying this function. The table is in the proper sort order (except for the duplicates), corresponding to the field "OrigSortOrder." I also created another field called "NewSortOrder," to put the new sort order in with the duplicates factored in. The way the client wants the data sorted is by the "SortOrder" field, except for duplicates which should be sorted consecutively with other duplicates. The function does not work properly, creating NewSortOrder values greater than the max of the OrigSortOrder. By the way, pardon my GoTo:

CODE]Function Populate()
Dim strAcctNum As String
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset, DupRS As DAO.Recordset
Dim strDupSQL As String

Dim intSortOrig As Integer, intSortNew As Integer, intNewNum As Integer

strSQL = "Select * from tblNewChart_Sorted Order by OrigSortOrder"
Set DB = CurrentDb
Set RS = DB.OpenRecordset(strSQL)

RS.MoveFirst


Do Until RS.EOF

RS.Edit
intSortOrig = RS!OrigSortOrder
intSortNew = Nz(RS!NewSortOrder, 0)
strAcctNum = RS!AcctNum

If RS!Dupe = -1 Then
RS.Edit
RS!NewSortOrder = Nz(DMax("NewSortOrder", "tblNewChart_Sorted"), 0) + 1
RS.Update
intNewNum = RS!NewSortOrder

strDupSQL = "Select OrigSortOrder, NewSortOrder from tblNewChart_Sorted Where OrigSortOrder > " & intSortOrig + 1 & "" _
& " And AcctNum = '" & strAcctNum & "'"

Set DupRS = DB.OpenRecordset(strDupSQL)

If DupRS.RecordCount = 0 Then
GoTo ZERO
End If
DupRS.MoveFirst
Do Until DupRS.EOF
DupRS.Edit
DupRS!NewSortOrder = Nz(DMax("NewSortOrder", "tblNewChart_Sorted"), 0) + 1

DupRS.Update
DupRS.MoveNext

Loop
ZERO:
Else 'No dupes
RS.Edit
RS!NewSortOrder = Nz(DMax("NewSortOrder", "tblNewChart_Sorted"), 0) + 1
RS.Update

End If





RS.MoveNext
Loop



MsgBox "All records sorted in table", vbCritical, "Sorting Finished"
[/code]
Mar 30 '17 #5

100+
P: 124
Trying again with the proper Code tags:

Expand|Select|Wrap|Line Numbers
  1. Function Populate()
  2. Dim strAcctNum As String
  3. Dim strSQL As String
  4. Dim DB As DAO.Database
  5. Dim RS As DAO.Recordset, DupRS As DAO.Recordset
  6. Dim strDupSQL As String
  7.  
  8. Dim intSortOrig As Integer, intSortNew As Integer, intNewNum As Integer
  9.  
  10. strSQL = "Select * from tblNewChart_Sorted Order by OrigSortOrder"
  11. Set DB = CurrentDb
  12. Set RS = DB.OpenRecordset(strSQL)
  13.  
  14. RS.MoveFirst
  15.  
  16.  
  17. Do Until RS.EOF
  18.  
  19.     RS.Edit
  20.     intSortOrig = RS!OrigSortOrder
  21.     intSortNew = Nz(RS!NewSortOrder, 0)
  22.     strAcctNum = RS!AcctNum
  23.  
  24.     If RS!Dupe = -1 Then
  25.         RS.Edit
  26.             RS!NewSortOrder = Nz(DMax("NewSortOrder", "tblNewChart_Sorted"), 0) + 1
  27.             RS.Update
  28.             intNewNum = RS!NewSortOrder
  29.  
  30.             strDupSQL = "Select OrigSortOrder, NewSortOrder from tblNewChart_Sorted Where OrigSortOrder > " & intSortOrig + 1 & "" _
  31.             & " And AcctNum = '" & strAcctNum & "'"
  32.  
  33.             Set DupRS = DB.OpenRecordset(strDupSQL)
  34.  
  35.         If DupRS.RecordCount = 0 Then
  36.             GoTo ZERO
  37.         End If
  38.         DupRS.MoveFirst
  39.         Do Until DupRS.EOF
  40.             DupRS.Edit
  41.                 DupRS!NewSortOrder = Nz(DMax("NewSortOrder", "tblNewChart_Sorted"), 0) + 1
  42.  
  43.             DupRS.Update
  44.         DupRS.MoveNext
  45.  
  46.         Loop
  47. ZERO:
  48.     Else 'No dupes
  49.         RS.Edit
  50.             RS!NewSortOrder = Nz(DMax("NewSortOrder", "tblNewChart_Sorted"), 0) + 1
  51.         RS.Update
  52.  
  53.     End If
  54.  
  55.  
  56.  
  57.  
  58.  
  59.     RS.MoveNext
  60. Loop
  61.  
  62.  
  63.  
  64. MsgBox "All records sorted in table", vbCritical, "Sorting Finished"
  65.  
  66.  
  67.  
  68. End Function
  69.  
Mar 30 '17 #6

jforbes
Expert 100+
P: 1,107
I think the best way to tackle this is to modify the original code that populates the SortOrder column in the first place, but it looks like that might not be possible.

So if we are given the SortOrder column to work with, then copying the current Sort into a Temp Column and then updating each record's SortOrder to the that of the Lowest available SortOrder for it's AcctNum should work. ...Which looks like you have started writing some code for.

I created a Test Environment and created two Queries that when run consecutively, seem to generate the NewSortOrder column data correctly.

The First Query (qryApplySortOrder1) sets/resets the NewSortOrder column to the current SortOrder column:
Expand|Select|Wrap|Line Numbers
  1. UPDATE SortOrderTest SET [NewSortOrder] = [OrigSortOrder]

The second Query (qryApplySortOrder2) then looks up the lowest NewSortOrder for an AcctNum, then updates the current NewSortOrder to the value found if it's lower than the current:
Expand|Select|Wrap|Line Numbers
  1. UPDATE SortOrderTest SET 
  2.    NewSortOrder=IIf(
  3.           Nz(DMin("NewSortOrder","SortOrderTest","AcctNum='" & [AcctNum] & "'"),99999)<[NewSortOrder]
  4.           ,DMin("NewSortOrder","SortOrderTest","AcctNum='" & [AcctNum] & "'")
  5.           ,[NewSortOrder]
  6.           ) 

I know you were going for a code solution using recordsets, but I try to use Queries when possible because they are typically faster.

So you could write some code like this to execute the Queries:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("qryApplySortOrder1")
  2. CurrentDb.Execute ("qryApplySortOrder2")

Lastly, if you then select your Table using the New sort order, followed by the old sort order, I think you'll get what your customers want.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tblNewChart_Sorted 
  2. ORDER BY NewSortOrder, OrigSortOrder

Picture of the test results:

Mar 31 '17 #7

100+
P: 124
JForbes, thanks for your efforts. I probably have not explained this well enough, but there are actually 2 sorts on the data. The first one is by SortOrder# and the next step is to group all duplicate AcctNum values together with the lowest sortorder for that AcctNum. For example, if there are records in SortOrder 1 and 2 that have duplicate AcctNum, they should be grouped together in SortOrder 1 and the duplicate record from SortOrder 2 would now show up in SortOrder 1. Most of the records have duplicate acctnums in multiple SortOrder#'s. I'm wondering if this is something that could be done in SQl Server with a rownumber() function or a self join, since it is like a sort within a sort. Thanks again for your help.
Apr 3 '17 #8

jforbes
Expert 100+
P: 1,107
Hey BikeToWork,

I still feel that the Solution I floated by you earlier should work for you. My understanding is that you have a SortOrder column that is maintained by some other process, that you would like to use as is, except in the case where records further down the sort order share an AcctNum with records towards the top of the sorted list. In this case the records further down are to be brought up and included right below the records with the same AcctNum.

To make it easier to communicate, I've included my test database so you can see how it works and give me pointers on what I'm missing. There is a Form that can be used to clear and define the NewSortOrder column. There is also a Report that has the Sort Order as "NewSortOrder, SortOrder"
Attached Files
File Type: zip SortOrder.zip (42.6 KB, 32 views)
Apr 3 '17 #9

100+
P: 124
JForbes, thanks for your help. The sortorder applies to a group of records where a certain criteria is true. There are multiple records for SortOrder1, SortOrder2, etc. Then the records need to be sorted by AcctNum of which there are many duplicates in all the sortorders. So if there is a record in SortOrder1 that has the same AcctNum as a record in SortOrder7, that record in SortOrder 7 should be displayed
after the SortOrder1 record of the same acctnum. I am totally open to changing the SortOrder procedure I wrote, if you have any ideas. So basically, what the client wants to do is have the records sorted by the Group (SortOrder) they belong to then they should be sorted by AcctNum, regardless of SortOrder. So if there is a dupe acctnum for a record in SortOrder1 and SortOrder7, the SortOrder7 record(s) should be sorted after the SortOrder1 record.


Expand|Select|Wrap|Line Numbers
  1. Function SortOrder_20170331()
  2. Dim DB As DAO.Database
  3. Dim RS As DAO.Recordset
  4. Dim strSQL As String, strNum As String, lngID As Long
  5. Set DB = CurrentDb
  6. strSQL = "Select AcctNum, Decision, DecisionReason, Duplicate, SortOrder from tblLSAC_20170321 Order By ID"
  7. Set RS = DB.OpenRecordset(strSQL)
  8.  
  9. RS.MoveFirst
  10. Do Until RS.EOF
  11.     strNum = RS!AcctNum
  12.     strDecision = RS![Decision]
  13.             Select Case strDecision
  14.                 Case "denied in full" 'SortOrder 1
  15.                     intSortOrder = 1
  16.  
  17.                 Case "denied in part" 'SortOrder 2
  18.                     intSortOrder = 2
  19.  
  20.                 Case "no decision" 'sort order 3-7
  21.                     strReason = Nz(RS![DecisionReason], "no reason")
  22.                     Select Case strReason
  23.                         Case "document doesn't meet guidelines"
  24.                             intSortOrder = 3
  25.                         Case "Doesn't Meet Documentation Guidelines"
  26.                             intSortOrder = 3
  27.                         Case "no documents"
  28.                             intSortOrder = 3
  29.                         Case "unable to process"
  30.                             intSortOrder = 4
  31.                         Case "incomplete file"
  32.                             intSortOrder = 5
  33.                         Case "file incomplete"
  34.                             intSortOrder = 5
  35.                         Case "N/A"
  36.                             intSortOrder = 6
  37.                         Case "late"
  38.                             intSortOrder = 7
  39.                         Case "no reason"
  40.                             intSortOrder = 8
  41.                         Case Else
  42.                             intSortOrder = 999
  43.  
  44.                     End Select
  45. '                Case "granted in full"     Waiting for Vu's query first to delete all only "Granted in Full" records
  46. '                intSortOrder = DLookup("SortOrder", "tblLSAC_20170321", "AcctNum = '" & strNum & "'")
  47.  
  48.                 End Select
  49.  
  50.  
  51.     RS.Edit
  52.  
  53.     If DCount("AcctNum", "[tblLSAC_20170321]", "AcctNum = '" & strNum & "'") > 1 Then
  54.         RS!Duplicate = -1
  55.     End If
  56.  
  57.     RS!SortOrder = intSortOrder
  58.     RS.Update
  59.  
  60.  
  61.     RS.MoveNext
  62.  
  63.  
  64. Loop
  65.  
  66. MsgBox "Procedure complete", vbCritical, "Proc Done"
  67.  
  68.  
  69.  
  70. End Function
  71.  
Apr 3 '17 #10

100+
P: 124
btw: there are 75,000 records to sort, not 1,000 as before.

Any help is much appreciated.
Apr 3 '17 #11

NeoPa
Expert Mod 15k+
P: 31,494
Hi Mike.

How about including the minimum (or maximum) [SortOrder] value by [AcctNum] in your record source. That way you can sort on that first followed by [AcctNum]?
Apr 4 '17 #12

jforbes
Expert 100+
P: 1,107
See if this is more to your liking. It more closely resembles your data based on the code you provided.
Attached Files
File Type: zip SortOrder.zip (49.3 KB, 35 views)
Apr 4 '17 #13

100+
P: 124
Thanks to all for your help with this. I eventually figured out that some sorts cannot be done with SQL (or at least I am not sure how to do them). This is a case of sorts, sub sorts and then further sorts within the same data so that data shows up the way the client wants it on a spreadsheet. The only way I could figure out how to do it was programmatically with the following code (after I first ran the sort code).

Expand|Select|Wrap|Line Numbers
  1. Function Sorts(intSortNum As Integer, strNum As String)
  2.  
  3. Dim DB As DAO.Database
  4. Dim RS As DAO.Recordset
  5. Dim strSQL As String
  6. Dim strAcctNum As String
  7. Dim lngSequential As Long
  8.  
  9. Dim strSubSortSQL As String
  10. Dim SubSortRS As DAO.Recordset
  11.  
  12.  
  13.  
  14.  
  15.  
  16. Set DB = CurrentDb
  17.  
  18. strSQL = "Select AcctNum, Duplicate, SortOrder, Sequential, SortCompleted from tblSAC_20170321_Sorted Where SortOrder =" & intSortNum & "" _
  19. & " And SortCompleted = 0 And Sequential Is Null And AcctNum = '" & strNum & "' Order By AcctNum, TestDate"
  20.  
  21.  
  22.  
  23. Set RS = DB.OpenRecordset(strSQL)
  24.  
  25. lngSequential = Nz(DMax("Sequential", "tblSAC_20170321_Sorted") + 1, 1)
  26. If RS.RecordCount > 0 Then
  27.     RS.MoveFirst
  28.  
  29.     'main recordset of table
  30.     Do Until RS.EOF
  31.  
  32.             RS.Edit
  33.             RS!Sequential = lngSequential
  34.             RS!SortCompleted = -1
  35.             RS.Update
  36.             lngSequential = lngSequential + 1
  37.             RS.MoveNext
  38.     Loop
  39. End If
  40.  
  41.     'matching AcctNum records of SortOrders > CurrentSortOrder
  42.     strSubSortSQL = "Select AcctNum,  SortOrder, Sequential, SortCompleted from tblSAC_20170321_Sorted Where SortOrder > " & intSortNum & "" _
  43.     & " And AcctNum = '" & strNum & "' And SortCompleted = 0 Order by SortOrder, AcctNum, TestDate"
  44.  
  45.     Set SubSortRS = DB.OpenRecordset(strSubSortSQL)
  46.     If SubSortRS.RecordCount > 0 Then
  47.         SubSortRS.MoveFirst
  48.         Do Until SubSortRS.EOF
  49.             SubSortRS.Edit
  50.             SubSortRS!Sequential = lngSequential
  51.             SubSortRS!SortCompleted = -1
  52.             SubSortRS.Update
  53.             lngSequential = lngSequential + 1
  54.             SubSortRS.MoveNext
  55.         Loop
  56.     End If
  57.  
  58.  
  59.  
  60.  
  61.  
  62.  
  63.  
  64.  
  65.  
  66. End Function
  67. Function SortsPopulate()
  68.  
  69. Dim DB As DAO.Database
  70. Dim RS As DAO.Recordset
  71. Dim intSortOrder As Integer
  72. Dim strAcctNum As String
  73.  
  74.  
  75. Set DB = CurrentDb
  76.  
  77. Set RS = DB.OpenRecordset("qrySortOrderAcctNum_GroupBy")
  78.  
  79. RS.MoveFirst
  80. Do Until RS.EOF
  81.     intSortOrder = RS!SortOrder
  82.     strAcctNum = RS!AcctNum
  83.     Call Sorts(intSortOrder, strAcctNum)
  84.  
  85.  
  86.     RS.MoveNext
  87.  
  88. Loop
  89.  
  90. MsgBox "all records sorted"
  91.  
  92.  
  93.  
  94.  
  95.  
  96.  
  97. End Function
  98.  
Apr 4 '17 #14

Post your reply

Sign in to post your reply or Sign up for a free account.