Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Convert data held in rows to columns

    I have data in a query in the following format:

    Name,Field,Data
    Joe Bloggs,Address,123 The Road
    Joe Bloggs,Client Type,Sole Trader
    Joe Bloggs,DOB,01/01/1980
    Bloggs & Co,Address,47 The Ave
    Bloggs & Co ,Client Type,Ltd Company
    Bloggs & Co,DOB,01/01/1970


    I want to get it into the following format so it is useful:

    Name,Address,Client Type,DOB

    Joe Bloggs,123 The Road,Sole Trader,01/01/1980
    Bloggs & Co,47 The Ave,Ltd Company,01/01/1970

    How (is it possible) do I go about doing this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Do you want to perform the data transformation in a permanent manner (i.e. create a new table) or do you just need it in a query or in a recordset?
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    The query is from my crm database which is off the shelf software.

    I want to run various reports from the data for example a list of all clients where client type = sole trader.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a simple function that will process the query and insert its data into a table named Tbl_Clients:
    Code:
    Function Convert_Raw_Data()
    
        Const c_SQL_Insert As String = "INSERT INTO Tbl_Clients ( Name, {Column} ) VALUES ( '{Name}', '{Data}' );"
        Const c_SQL_Update As String = "UPDATE Tbl_Clients SET {Column} ='{Data}' WHERE Name = '{Name}';"
        
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim booInsert As Boolean
        Dim strData As String
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Qry_Raw_Data", dbOpenSnapshot)
        With rst
            Do Until .EOF
                strSQL = ""
                If DCount("Name", "Tbl_Clients", "Name='" & !Name & "'") = 0 Then booInsert = True Else booInsert = False
                Select Case !Field
                    Case "Address"
                        If booInsert = True Then
                            strSQL = Replace(Replace(Replace(c_SQL_Insert, "{Column}", "Address"), "{Name}", !Name), "{Data}", !Data)
                        Else
                            strSQL = Replace(Replace(Replace(c_SQL_Update, "{Column}", "Address"), "{Name}", !Name), "{Data}", !Data)
                        End If
                    Case "Client Type"
                        If booInsert = True Then
                            strSQL = Replace(Replace(Replace(c_SQL_Insert, "{Column}", "Client_Type"), "{Name}", !Name), "{Data}", !Data)
                        Else
                            strSQL = Replace(Replace(Replace(c_SQL_Update, "{Column}", "Client_Type"), "{Name}", !Name), "{Data}", !Data)
                        End If
                    Case "DOB"
                        If booInsert = True Then
                            strSQL = Replace(Replace(Replace(c_SQL_Insert, "{Column}", "DOB"), "{Name}", !Name), "{Data}", !Data)
                        Else
                            strSQL = Replace(Replace(Replace(c_SQL_Update, "{Column}", "DOB"), "{Name}", !Name), "{Data}", !Data)
                        End If
                    Case Else
                        MsgBox "Unknown Data type: " & !Data_Type, vbInformation, "Convert_Raw_Data"
                End Select
                If Len(strSQL) > 0 Then dbs.Execute strSQL, dbFailOnError
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Tbl_Client is defined as:
    Name, Text
    Address, Text
    Client_Type, Text
    DOB, Text (you might want to change this one into Date/Tame)

    In my example, the query is named Qry_Raw_Data and has the followin columns:
    Name, Text
    Field, Text
    Data, Text

    You can base your reports on queries using Tbl_Clients as data source.
    Have a nice day!

  5. #5
    Join Date
    Aug 2011
    Location
    London, UK
    Posts
    3
    Quote Originally Posted by Sinndho View Post
    Here's a simple function that will process the query and insert its data into a table named Tbl_Clients:
    Code:
    Function Convert_Raw_Data()
    
        Const c_SQL_Insert As String = "INSERT INTO Tbl_Clients ( Name, {Column} ) VALUES ( '{Name}', '{Data}' );"
        Const c_SQL_Update As String = "UPDATE Tbl_Clients SET {Column} ='{Data}' WHERE Name = '{Name}';"
        
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim booInsert As Boolean
        Dim strData As String
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Qry_Raw_Data", dbOpenSnapshot)
        With rst
            Do Until .EOF
                strSQL = ""
                If DCount("Name", "Tbl_Clients", "Name='" & !Name & "'") = 0 Then booInsert = True Else booInsert = False
                Select Case !Field
                    Case "Address"
                        If booInsert = True Then
                            strSQL = Replace(Replace(Replace(c_SQL_Insert, "{Column}", "Address"), "{Name}", !Name), "{Data}", !Data)
                        Else
                            strSQL = Replace(Replace(Replace(c_SQL_Update, "{Column}", "Address"), "{Name}", !Name), "{Data}", !Data)
                        End If
                    Case "Client Type"
                        If booInsert = True Then
                            strSQL = Replace(Replace(Replace(c_SQL_Insert, "{Column}", "Client_Type"), "{Name}", !Name), "{Data}", !Data)
                        Else
                            strSQL = Replace(Replace(Replace(c_SQL_Update, "{Column}", "Client_Type"), "{Name}", !Name), "{Data}", !Data)
                        End If
                    Case "DOB"
                        If booInsert = True Then
                            strSQL = Replace(Replace(Replace(c_SQL_Insert, "{Column}", "DOB"), "{Name}", !Name), "{Data}", !Data)
                        Else
                            strSQL = Replace(Replace(Replace(c_SQL_Update, "{Column}", "DOB"), "{Name}", !Name), "{Data}", !Data)
                        End If
                    Case Else
                        MsgBox "Unknown Data type: " & !Data_Type, vbInformation, "Convert_Raw_Data"
                End Select
                If Len(strSQL) > 0 Then dbs.Execute strSQL, dbFailOnError
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Function
    Tbl_Client is defined as:
    Name, Text
    Address, Text
    Client_Type, Text
    DOB, Text (you might want to change this one into Date/Tame)

    In my example, the query is named Qry_Raw_Data and has the followin columns:
    Name, Text
    Field, Text
    Data, Text

    You can base your reports on queries using Tbl_Clients as data source.
    Very clear. Good Sinndho. Thanks for sharing!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •