how to tranform data in Access - urgent

[SKV]

I have a big data in the following format

(Headings) ProjectID ; Jan ;Feb
(Data row 1) A; 1 ; 2
(Data row 2) B; 4 ;Null

I want to transfer this data in the following format

(Headings) ProjectID ; Month ; Value
(Data row 1) A ; Jan ;1
(Data row 2) A ;Feb; 2
(Data row 3) B; Jan ; 4
(Data row 4) B; Feb ; Null

Please advice the quick and easy method as my data is very huge and I cannot do it manually.

Thanks
SKV

 

[alansidman]

Take a look at this video on cross tab queries. Then once you have the query completed, then do a make table query based upon your original query.

http://www.datapigtechnologies.com/flashfiles/crosstab.html

 

[SKV]

This video is showing the reverse of what I want, any other idea?

Take a look at this video on cross tab queries. Then once you have the query completed, then do a make table query based upon your original query.

http://www.datapigtechnologies.com/flashfiles/crosstab.html

 

[SydneyGeek]

You can use a UNION query. Something like...

SELECT [ProjectID],"Jan" As TxMonth, [Jan] As Amount FROM MyTable
UNION
SELECT [ProjectID],"Feb" As TxMonth, [Feb] As Amount FROM MyTable
UNION
SELECT [ProjectID],"Mar" As TxMonth, [Mar] As Amount FROM MyTable
... etc

Save that query, then build a make-table query based on that query and run it to create your new table with the transformed data

Denis

 

[SKV]

Thanks !!! your the man !!!!



Thanks a lot, its the exact thing I was looking for


Is there a way to instead of manual adding the month names, I can have it pulled automatically from a list. this would be great as my Txmonth list is quite big (almost 200 items)

You can use a UNION query. Something like...

SELECT [ProjectID],"Jan" As TxMonth, [Jan] As Amount FROM MyTable
UNION
SELECT [ProjectID],"Feb" As TxMonth, [Feb] As Amount FROM MyTable
UNION
SELECT [ProjectID],"Mar" As TxMonth, [Mar] As Amount FROM MyTable
... etc

Save that query, then build a make-table query based on that query and run it to create your new table with the transformed data

Denis

 

[SydneyGeek]

You'll need to generate a list of the field names first. Do this:
1. Create a table called ztblFields with a single text field, called FieldName. Close the table.
2. Paste the following code into a new module (Alt+F11, Insert > Module, then Paste)
2a. Set a reference to the Microsoft DAO 3.6 Object Library.
3. I tested this on a table called AllTime. Change that in the code, to the table whose field names you want to extract.
4. To extract the names place your cursor anywhere in the code and press F5.

Function WriteFields()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sSQL As String
    
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("AllTime")
    DoCmd.SetWarnings False
    For Each fld In tdf.Fields
        sSQL = "INSERT INTO ztblFields ( FieldName ) " _
            & "Values ( '" & fld.Name & "' );"
        DoCmd.RunSQL sSQL
    Next fld
    DoCmd.SetWarnings True
    
    Set tdf = Nothing
    Set dbs = Nothing
End Function

Working on the next bit...

Denis

 

[SydneyGeek]

Next part... create the SQL statement
1. Place this function in the same module and run it. You will end up with a text file called UNION_query.txt, in the same folder as the database.
2. Open the file in Notepad and use File > Replace to replace all double quotes with nothing
3. Now replace all apostrophes with double quotes
4. Now remove the last UNION line
5. Copy everything, then create a new query in Access but don't select any tables. Click the SQL button and paste. Try running that query and see how you go

Function WriteSQL()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strInput As String
    Dim intFile As Integer
    Dim strFile As String
    
    'define variables and create an output file
    strFile = CurrentProject.Path & "\UNION_Query.txt"
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblFields").OpenRecordset
    intFile = FreeFile
    Open strFile For Output As #intFile
    With rst
        .MoveFirst
        Do Until .EOF
            strInput = "SELECT [ProjectID], '" & !FieldName & "' As TxtMonth, [" & !FieldName & "] As Amount "
            Write #intFile, strInput
            Write #intFile, "UNION "
            .MoveNext
        Loop
    End With
    Close #intFile
    
    'clean up references
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Denis

 

[SKV]

Denis,

Can we make this more automated? that I load the initial table and get the final result table???

Thanks for your help

Next part... create the SQL statement
1. Place this function in the same module and run it. You will end up with a text file called UNION_query.txt, in the same folder as the database.
2. Open the file in Notepad and use File > Replace to replace all double quotes with nothing
3. Now replace all apostrophes with double quotes
4. Now remove the last UNION line
5. Copy everything, then create a new query in Access but don't select any tables. Click the SQL button and paste. Try running that query and see how you go

Function WriteSQL()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strInput As String
    Dim intFile As Integer
    Dim strFile As String
    
    'define variables and create an output file
    strFile = CurrentProject.Path & "\UNION_Query.txt"
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblFields").OpenRecordset
    intFile = FreeFile
    Open strFile For Output As #intFile
    With rst
        .MoveFirst
        Do Until .EOF
            strInput = "SELECT [ProjectID], '" & !FieldName & "' As TxtMonth, [" & !FieldName & "] As Amount "
            Write #intFile, strInput
            Write #intFile, "UNION "
            .MoveNext
        Loop
    End With
    Close #intFile
    
    'clean up references
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Denis

 

[SydneyGeek]

Try this version. It writes the SQL out to a query called qryUNIONtest -- you will need to create the query first (put anything you like in it -- the query gets overwritten anyway). Also you will need to change the table names in both routines.

Function WriteFields()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sSQL As String
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM ztblTableFields"
    DoCmd.SetWarnings True
 
    Set dbs = CurrentDb()
    Set tdf = dbs.TableDefs("AllTime")
    DoCmd.SetWarnings False
    For Each fld In tdf.Fields
        sSQL = "INSERT INTO ztblTableFields ( FieldName ) " _
            & "Values ( '" & fld.Name & "' );"
        DoCmd.RunSQL sSQL
    Next fld
    DoCmd.SetWarnings True
 
    Set tdf = Nothing
    Set dbs = Nothing
End Function
 
Function CreateBigUnionQuery()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset, _
        rst1 As DAO.Recordset
    Dim strInput As String, _
        strOut As String
 
    'define variables and create an output file
    strOut = ""
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("ztblTableFields").OpenRecordset
    Set rst1 = dbs.TableDefs("ztmpSQL").OpenRecordset
    With rst
        .MoveFirst
        Do Until .EOF
            strInput = "SELECT [ProjectID], '" & !FieldName & "' As TxtMonth, [" & !FieldName & "] As Amount FROM tblMyTable UNION "
            strOut = strOut & strInput
            .MoveNext
        Loop
    End With
    strOut = Left(strOut, Len(strOut) - 7)
    dbs.QueryDefs("qryUNIONTest").SQL = strOut
 
    'clean up references
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Denis