Sort Angst

[BikeToWork]

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.

[jforbes]

Maybe, it would kind of depend on your data. What is in the SortOrder Field and how is it populated.

[BikeToWork]

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.

[jforbes]

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.

[BikeToWork]

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]

[BikeToWork]

Trying again with the proper Code tags:

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"
 
 
 
End Function
 

[jforbes]

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:

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:

UPDATE SortOrderTest SET 
   NewSortOrder=IIf(
          Nz(DMin("NewSortOrder","SortOrderTest","AcctNum='" & [AcctNum] & "'"),99999)<[NewSortOrder]
          ,DMin("NewSortOrder","SortOrderTest","AcctNum='" & [AcctNum] & "'")
          ,[NewSortOrder]
          ) 

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:

CurrentDb.Execute ("qryApplySortOrder1")
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.

SELECT * FROM tblNewChart_Sorted 
ORDER BY NewSortOrder, OrigSortOrder

Picture of the test results:

[BikeToWork]

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.

[jforbes]

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"

[BikeToWork]

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.

Function SortOrder_20170331()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String, strNum As String, lngID As Long
Set DB = CurrentDb
strSQL = "Select AcctNum, Decision, DecisionReason, Duplicate, SortOrder from tblLSAC_20170321 Order By ID"
Set RS = DB.OpenRecordset(strSQL)
 
RS.MoveFirst
Do Until RS.EOF
    strNum = RS!AcctNum
    strDecision = RS![Decision]
            Select Case strDecision
                Case "denied in full" 'SortOrder 1
                    intSortOrder = 1
 
                Case "denied in part" 'SortOrder 2
                    intSortOrder = 2
 
                Case "no decision" 'sort order 3-7
                    strReason = Nz(RS![DecisionReason], "no reason")
                    Select Case strReason
                        Case "document doesn't meet guidelines"
                            intSortOrder = 3
                        Case "Doesn't Meet Documentation Guidelines"
                            intSortOrder = 3
                        Case "no documents"
                            intSortOrder = 3
                        Case "unable to process"
                            intSortOrder = 4
                        Case "incomplete file"
                            intSortOrder = 5
                        Case "file incomplete"
                            intSortOrder = 5
                        Case "N/A"
                            intSortOrder = 6
                        Case "late"
                            intSortOrder = 7
                        Case "no reason"
                            intSortOrder = 8
                        Case Else
                            intSortOrder = 999
 
                    End Select
'                Case "granted in full"     Waiting for Vu's query first to delete all only "Granted in Full" records
'                intSortOrder = DLookup("SortOrder", "tblLSAC_20170321", "AcctNum = '" & strNum & "'")
 
                End Select
 
 
    RS.Edit
 
    If DCount("AcctNum", "[tblLSAC_20170321]", "AcctNum = '" & strNum & "'") > 1 Then
        RS!Duplicate = -1
    End If
 
    RS!SortOrder = intSortOrder
    RS.Update
 
 
    RS.MoveNext
 
 
Loop
 
MsgBox "Procedure complete", vbCritical, "Proc Done"
 
 
 
End Function
 

[BikeToWork]

btw: there are 75,000 records to sort, not 1,000 as before.

Any help is much appreciated.

[NeoPa]

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]?

[jforbes]

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

[BikeToWork]

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).

Function Sorts(intSortNum As Integer, strNum As String)
 
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim strAcctNum As String
Dim lngSequential As Long
 
Dim strSubSortSQL As String
Dim SubSortRS As DAO.Recordset
 
 
 
 
 
Set DB = CurrentDb
 
strSQL = "Select AcctNum, Duplicate, SortOrder, Sequential, SortCompleted from tblSAC_20170321_Sorted Where SortOrder =" & intSortNum & "" _
& " And SortCompleted = 0 And Sequential Is Null And AcctNum = '" & strNum & "' Order By AcctNum, TestDate"
 
 
 
Set RS = DB.OpenRecordset(strSQL)
 
lngSequential = Nz(DMax("Sequential", "tblSAC_20170321_Sorted") + 1, 1)
If RS.RecordCount > 0 Then
    RS.MoveFirst
 
    'main recordset of table
    Do Until RS.EOF
 
            RS.Edit
            RS!Sequential = lngSequential
            RS!SortCompleted = -1
            RS.Update
            lngSequential = lngSequential + 1
            RS.MoveNext
    Loop
End If
 
    'matching AcctNum records of SortOrders > CurrentSortOrder
    strSubSortSQL = "Select AcctNum,  SortOrder, Sequential, SortCompleted from tblSAC_20170321_Sorted Where SortOrder > " & intSortNum & "" _
    & " And AcctNum = '" & strNum & "' And SortCompleted = 0 Order by SortOrder, AcctNum, TestDate"
 
    Set SubSortRS = DB.OpenRecordset(strSubSortSQL)
    If SubSortRS.RecordCount > 0 Then
        SubSortRS.MoveFirst
        Do Until SubSortRS.EOF
            SubSortRS.Edit
            SubSortRS!Sequential = lngSequential
            SubSortRS!SortCompleted = -1
            SubSortRS.Update
            lngSequential = lngSequential + 1
            SubSortRS.MoveNext
        Loop
    End If
 
 
 
 
 
 
 
 
 
End Function
Function SortsPopulate()
 
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intSortOrder As Integer
Dim strAcctNum As String
 
 
Set DB = CurrentDb
 
Set RS = DB.OpenRecordset("qrySortOrderAcctNum_GroupBy")
 
RS.MoveFirst
Do Until RS.EOF
    intSortOrder = RS!SortOrder
    strAcctNum = RS!AcctNum
    Call Sorts(intSortOrder, strAcctNum)
 
 
    RS.MoveNext
 
Loop
 
MsgBox "all records sorted"
 
 
 
 
 
 
End Function