ACC2000: How to Sort a Field of Numbers Based on All Permutations (247650)

SUMMARY

In a query, you may want to sort a field based on all permutations of each group of digits. For instance, all of the following records would appear together in the sort:

5678, 6785, 7856, 8567, 7568, 8756

These records would appear together because they are all based on the digits 5678. What sequence the digits of each number are in is not relevant, as long as the matching numbers appear together in the sort. This article demonstrates a way to do this.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

MORE INFORMATION

You can accomplish this task by defining an extra column in your query that contains a custom function that takes any number and sorts its digits from left to right. For example, the function takes 8675 and turns it into 5678. If the function is given 6758, the function turns this number into 5678 as well. You can call a number like 5678 the "common" number.

By sorting on the column of common numbers, you can get a list of numbers grouped together according to the individual digits that they contain. The following steps demonstrate how to do this:

Create a new Access database.

Create a new table with the following specifications:

   Table: MyNumbers
   ----------------------------
   Field Name: ID
   Data Type: AutoNumber

   Field Name: TestNum
   Data Type: Number

Type the following data into the table:

   ID      TestNum
   ---     -------
   1       1111
   2       1122
   3       1212
   4       1234
   5       2569
   6       5269
   7       9652
   8       4956
   9       4587
   10      8745
   11      5678
   12      6785
   13      7568
   14      7856
   15      8567
   16      8756
   17      5689
   18      8899
   19      9898
   20      8999
   21      9998

In the Database window, click Modules, click New, and then type the following code in the new module:

Function OrderString(xString As String)

Dim stringArray() As String
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp As String
Dim returnString As String

stringlen = Len(Format(xString, 0))

ReDim stringArray(1 To stringlen)
    
For i = 1 To stringlen
    stringArray(i) = Mid(xString, i, 1)
Next i

    First = LBound(stringArray)
    Last = UBound(stringArray)
    For i = First To Last - 1
        For j = i + 1 To Last
      
            If stringArray(i) > stringArray(j) Then
                Temp = stringArray(j)
                stringArray(j) = stringArray(i)
                stringArray(i) = Temp
            End If
        Next j
    Next i

For i = 1 To stringlen
    returnString = returnString & stringArray(i)
Next i
OrderString = returnString
End Function

On the File menu, click Save <database name>. Click Yes in the Save dialog box.
In the Save As dialog box, you can leave the module name as is, and then click OK.
On the File menu click Close and Return to Microsoft Access.
In the Database window, click Queries, and then click New. Click Design View, and then click OK.
Add the MyNumbers table to the query design grid, and then close the Show Table dialog box.

Create a new query with the following characteristics:

   Query Name: TestSort
   --------------------------------------
   Field: TestNum
   Sort:  Leave Blank

   Field: CommonNum: OrderString(TestNum)
   Sort:  Ascending

On the Query menu, click Run. Note that you receive the following results:

   TestNum   CommonNum
   -------   ---------
   1111      1111
   1122      1122
   1212      1122
   1234      1234
   2569      2569
   5269      2569
   9652      2569
   4956      4569
   4587      4578
   8745      4578
   5678      5678
   6785      5678
   7568      5678
   7856      5678
   8567      5678
   8756      5678
   5689      5689
   8899      8899
   9898      8899
   8999      8999
   9998      8999

NOTE: If you do not want to see the CommonNum column, you can return to Design view and click to clear the Show check box for that column.