'how to display columns in specific order, according to field value in MS Access

I want to display a query but the column order needs to be sorted according to the value in the column, field with greater value as column 1, then second greater value field as column 2, and so on. The query will only produce one row as it shows data for the current month. I know I could do this in VBA, with a bubble sort or something, and build the SQL query accordingly, but I was wondering if there was a quicker way to do this. Data is like this: DataMonth, Data1, Data2, Data3,... all the way to Data8. Please don't mention data structure is bad, as I am only trying to help someone here, and it is a bit too late to rebuild their tables structures...

sqldatabasems-access


Solution 1:[1]

No need to sort in the VBA. First get a record set that lists the columns in the right order with the Column Name as one of the columns - order that query by the value in each column. I used a query that got each single column with UNION e.g. here is my entire module

Option Compare Database
Option Explicit

Private Sub ReorderColumns()

    Dim mnth As Integer
    mnth = 1      'CHANGE MONTH

    Dim qry As String
    Dim rs As DAO.Recordset

    qry = GetSQL(mnth)
    Set rs = CurrentDb.OpenRecordset(qry)
    
    Dim i As Integer: i = 0    'Count of columns added to query
    qry = "SELECT "
    While Not rs.EOF
        i = i + 1
        qry = qry & rs.Fields("ColName") & IIf(i < 8, ",", "")
        rs.MoveNext
    Wend

    qry = qry & " FROM Table1 WHERE DataMonth = %s"
    qry = stuffStr(qry, mnth)
    rs.Close

    'Get the results in the order you need
    Set rs = CurrentDb.OpenRecordset(qry)
    
    'Do something with the data here
    
    rs.Close
    Set rs = Nothing

End Sub
Public Function GetSQL(mnth As Integer) As String
    
    Dim outp As String

    outp = "SELECT 'Data1' as ColName, Data1 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data2' as ColName, Data2 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data3' as ColName, Data3 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data4' as ColName, Data4 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data5' as ColName, Data5 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data6' as ColName, Data6 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data7' as ColName, Data7 as DataValue FROM Table1 WHERE DataMonth = %s UNION ALL " & _
                "SELECT 'Data8' as ColName, Data8 as DataValue FROM Table1 WHERE DataMonth = %s ORDER BY 2"
    GetSQL = stuffStr(outp, mnth, mnth, mnth, mnth, mnth, mnth, mnth, mnth)


End Function
Function stuffStr(str As String, ParamArray subs()) As String

    Dim i As Long
    Dim outp As String
    
    outp = str
    
    For i = 0 To UBound(subs)
        outp = Replace(Expression:=outp, Find:="%s", Replace:=subs(i), Count:=1, Compare:=vbTextCompare)
    Next
    
    stuffStr = outp

End Function

Not the most efficient way because of all the string manipulation, but it works

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 CHill60

Related Questions