Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: Any way to convert columns to rows? See inside for an example

    Hello,
    As usual, I want to do something "strange".

    I what to pass from this:
    Code:
    Field	J	F	M	A	M
    A1	7	5	3	1	-1
    A2	78	546	1014	1482	1950
    B1	4	456	908	1360	1812
    B2	5	54	103	152	201
    to this:

    Code:
    Field	Month	Value
    A1	J	7
    A2	J	78
    B1	J	4
    B2	J	5
    A1	F	5
    A2	F	546
    B1	F	456
    B2	F	54
    A1	M	3
    A2	M	1014
    B1	M	908
    B2	M	103
    A1	A	1
    A2	A	1482
    B1	A	1360
    B2	A	152
    A1	M	-1
    A2	M	1950
    B1	M	1812
    B2	M	201
    Any way to do it in VBA? in a Query? in Excel?

    Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try a UNION query:

    SELECT Field, "J" AS Month, J AS Value
    FROM TableName
    UNION ALL
    SELECT Field, "F" AS Month, F AS Value
    FROM TableName
    UNION ALL
    ...
    Paul

  3. #3
    Join Date
    Apr 2008
    Posts
    189
    Yeah, I'd tried that solution... but it's a bit slow.
    And also, it has to be "by-hand".

    Any other ideas?

    Thanks! for your reply and help.
    Saludos,

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, I'd tried that solution... but it's a bit slow.
    Be thankful it works With such a strange design, you're going to have to accept "disadvantages".

    And also, it has to be "by-hand".
    What does this mean? You want to do it without automation?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Paul, the problem you have is that your data is not normalised, the resultset you're asking for is closer to what you want to store the data as anyway!

    Use STs suggestion to sort your data out (create a new table, INSERT INTO <that select statement>) and these sorts of problems will dissapear
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You could write some code to read each record then add a record for each field in that record to a new table. This would give you the format that you require.

  7. #7
    Join Date
    Apr 2008
    Posts
    189
    OK, you convince me.
    I will do it that way

    And George, you are just right: my problem is that I have the information not well sorted.

  8. #8
    Join Date
    Apr 2008
    Posts
    189
    Also, any ideas how to do that in Excel?
    I mean, does anyone has any VBA code to do that? Or know how to look for it?

    Thanks!

  9. #9
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Attached is a sample using VBA to convert the table into your output requirement. Run the module mod_convert_batch_month
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2008
    Posts
    189

    Wink

    Quote Originally Posted by Poppa Smurf
    Attached is a sample using VBA to convert the table into your output requirement. Run the module mod_convert_batch_month
    Thanks!
    Thank you!

  11. #11
    Join Date
    Apr 2008
    Posts
    189
    I also leave attached an Excel version for doing that with VBA
    Attached Files Attached Files

  12. #12
    Join Date
    Apr 2008
    Posts
    189

    Wink

    Here is a mod of the "Poppa Smurf" to make the VBA code more "automatic".

    Code:
    Option Compare Database
    'Option Explicit
    
    Public Sub sRecorrer_tFuenteDatoTabla()
    Set rsFuenteDatoTabla = CurrentDb.OpenRecordset("tFuente Dato Tabla")
        rsFuenteDatoTabla.MoveFirst
    
    Dim vColumnasFuenteDatoTabla(50) As String
    
    Do While Not rsFuenteDatoTabla.EOF
        Call fLimpiarArray(vColumnasFuenteDatoTabla, 0)
        vColumnasFuenteDatoTabla(0) = rsFuenteDatoTabla("Nombre de la tabla")
        vColumnasFuenteDatoTabla(1) = rsFuenteDatoTabla("Dato")
        vColumnasFuenteDatoTabla(2) = rsFuenteDatoTabla("Fuente")
        vColumnasFuenteDatoTabla(3) = rsFuenteDatoTabla("TextoAlPie")
    
        MsgBox "Empezando la tabla: " & vColumnasFuenteDatoTabla(0)
        Call sCrearNuevoFormatoDeTabla(vColumnasFuenteDatoTabla, "tTemp")
        
        rsFuenteDatoTabla.MoveNext
    Loop
    
    End Sub
    Code:
    Public Sub sCrearNuevoFormatoDeTabla(vColumnasFuenteDatoTabla, vNombreTablaDestino)
    
    Dim vEncabezadosFila(50, 1) As String
        vEncabezadosFila(0, 0) = "IDFecha" 'El nombre en la tabla origen
        vEncabezadosFila(0, 1) = "IDFecha" 'El nombre en la tabla destino
        vEncabezadosFila(1, 0) = "Nombre tabla"
        vEncabezadosFila(1, 1) = "Nombre tabla"
        vEncabezadosFila(2, 0) = "anho_desde"
        vEncabezadosFila(2, 1) = "Año desde"
        vEncabezadosFila(3, 0) = "mes_desde"
        vEncabezadosFila(3, 1) = "Mes desde"
        vEncabezadosFila(4, 0) = "anho_hasta"
        vEncabezadosFila(4, 1) = "Año hasta"
        vEncabezadosFila(5, 0) = "mes_hasta"
        vEncabezadosFila(5, 1) = "Mes hasta"
        iEncabezadosFila = 5
    
    ''Nombre de la tabla que recorro
    'vNombreTablaOrigen = "tOrigen"
    'vNombreTablaDestino = "tTemp"
    
    'Definiciones
    Dim rsTablaOrigen As DAO.Recordset
    Dim rsTablaDestino As DAO.Recordset
    Dim vEncontrado As Boolean
    Dim i, j, k, n As Integer
    Set rsTablaOrigen = CurrentDb.OpenRecordset(vColumnasFuenteDatoTabla(0))
    Set rsTablaDestino = CurrentDb.OpenRecordset(vNombreTablaDestino)
    Set rscolumnas = CurrentDb.OpenRecordset("tColumnas")
    
    'Aca grabo los encabezados de filas
    Dim vDatos(256, 2) As Variant 'Cantidad de columnas
        
    'Empiezo a recorrer los elementos de la tabla
    With rsTablaOrigen
        Do Until .EOF
            
            'Limpio el vector
            Call fLimpiarArray(vDatos, 2)
            
            'Busco los fijos en base a la lista de arriba
            For n = 0 To .Fields.Count - 1
                vEncontrado = False
                For j = 0 To iEncabezadosFila
                    If .Fields(n).Name = vEncabezadosFila(j, 0) Then
                        vEncontrado = True
                        Exit For
                    End If
                Next j
                If vEncontrado Then
                    vDatos(n, 0) = .Fields(n).Name
                    vDatos(n, 1) = .Fields(n).Value
                    vDatos(n, 2) = vEncabezadosFila(j, 1)
                Else
                    Exit For 'Esto es clave, marco donde terminan los campos fijos
                End If
            Next n
            
            'Recorro los campos
            For i = n To .Fields.Count - 1 'Empieza desde donde terminan los campos fijos "n"
            rsTablaDestino.AddNew
                'Pongo los fijos
                For k = 0 To n - 1
                    rsTablaDestino(vDatos(k, 2)) = vDatos(k, 1)
                Next k
                
                'Datos de margen
                rsTablaDestino("IDFecha") = Now
                rsTablaDestino("Nombre tabla") = vColumnasFuenteDatoTabla(0)
                rsTablaDestino("Observaciones") = vColumnasFuenteDatoTabla(3)
                rsTablaDestino("Fuente") = vColumnasFuenteDatoTabla(2)
                rsTablaDestino("Grupo de datos") = vColumnasFuenteDatoTabla(1)
                
                'Busco los datos de unidad y tipo de dato
                rscolumnas.FindFirst ("[Tabla] = """ & vColumnasFuenteDatoTabla(1) & """ AND [Columna] = """ & .Fields(i).Name & """")
                    If rscolumnas.NoMatch Then MsgBox "No se encontró la columna. Parar todo"
                'Los campos variables
                rsTablaDestino("Primer detalle") = rscolumnas("Tipo de producto") '.Fields(i).Name
                rsTablaDestino("Segundo detalle") = rscolumnas("Columna sin unidad")
                rsTablaDestino("Unidad de medida") = rscolumnas("Unidad actual") 'Mid(.Fields(i).Name, InStrRev(.Fields(i).Name, " ") + 1)
                
                'El número
                rsTablaDestino("Cantidad") = .Fields(i).Value
            rsTablaDestino.Update
            Next i
          
            .MoveNext
        Loop
    End With
    
    End Sub
    Code:
    Sub fLimpiarArray(vArray, vDimension)
    For j = 0 To vDimension
    For i = LBound(vArray) To UBound(vArray)
        If vDimension = 0 Then
            vArray(i) = ""
        Else
            vArray(i, j) = ""
        End If
    Next i
    Next j
    End Sub

Posting Permissions

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