Sort Values from different fields but same row in a query

Evelyn · Feb 27, 2004

Hi all,

Here is my problem:

Sort Values from different fields but same row in a query:

I have 13 columns of numerical data that i have to sort in ascending order
here is a sample of how my query result looks like in the datasheet view:
Column: Field1 Field2 Field3 ... ... Field12 Field13
Row Value: 3 5 4 ... ... 8 7

Can i seek for help on what i could do to sort the results in the row in ascending order like the below:
Row Value: 3 4 5 ... ... 7 8

THANKS !!!!!!!!!!!!!

namliam · Feb 27, 2004

You may be able to do something in formview, but that is the only way i can think off....

Regards

dcx693 · Feb 27, 2004

Evelyn, there are a few ways to do this, none of them really nice and simple. Do you happen to have a copy of Excel on that same machine running Access? Will you always have 13 number fields?

Evelyn · Mar 11, 2004

Thanks for offering your help dcx693,

Answers to your question:
1. Yes i have a copy of excel on the same machine running access
2. Yes again, the number fields are always 13

Thanks
Evelyn

dcx693 said:
Evelyn, there are a few ways to do this, none of them really nice and simple. Do you happen to have a copy of Excel on that same machine running Access? Will you always have 13 number fields?

dcx693 · Mar 11, 2004

Well, first off, I'll tell you that your data is really not in a desirable format. The best long-term approach would be to have each of your 13 fields within their own row. That is, assuming you could alter the database structure.

If you can't adjust the structure, then you wind up jumping through hoops to get simple results...like sorting 13 values.

Here are some options:

Use a custom function with a custom sort routine. You'll have to supply a sorting routine because Access has no built-in way of sorting arrays.
Use a custom function without a sort routine. You'll need to save the array of 13 values to a temp table, open a recordset on the table that sorts the values.
Use the Excel Small() functionSince you've got Excel on your machine, you can make an automation call to it. You can use a custom function like this to get the nth smallest number in the array. You'll need to call it repeatedly to get all the results you want and the speed might be unacceptable. You'll have to try it to find out.
Code:
```
Function GetNthNumber(intPosition As Integer, _
    ParamArray dblNums() As Variant) As Double
Dim obj As Object

    Set obj = CreateObject("Excel.Application")
        GetNthNumber = obj.Small(Array(dblNums()), intPosition)
    obj.Quit
    Set obj = Nothing

End Function
```

dcx693 · Mar 11, 2004

Upon further reflection (and testing), I've found a potentially better solution to this. It's a variation on option 2.

You can send the 13 fields as an array to your custom function, then build a recordset in memory without needing to persist it in a table. This should make it run faster since it's all happening in memory. You can then build a recordset based on the original record and sort it - again, all in memory.

Here's a sample code listing:

Code:

Function GetNthNumber2(intPosition As Integer, _
    ParamArray dblNumbers() As Variant) As Variant
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim intX As Integer

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    
    rst.Fields.Append "Numbers", adDouble
    rst.Open
    
    For intX = 0 To UBound(dblNumbers())
        rst.AddNew
        rst.Fields("Numbers") = dblNumbers(intX)
        rst.MoveNext
    Next intX
    
    Set rst2 = New ADODB.Recordset
    rst2.Open rst
    rst2.Sort = "Numbers"
    
    rst2.Move intPosition - 1
    GetNthNumber2 = rst2.Fields("Numbers")
    
    rst2.Close
    rst.Close
    Set rst2 = Nothing
    Set rst = Nothing
    Set cnn = Nothing

End Function

Sort Values from different fields but same row in a query

Evelyn

Guest

namliam

The Mailman - AWF VIP

dcx693

Registered User.

Evelyn

Guest

dcx693

Registered User.

dcx693

Registered User.

Similar threads

Users who are viewing this thread