Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    6

    Unanswered: Transposing Fields to Rows

    How do I transpose fields to rows, maintaining some fields as fields?
    Here's an example:

    (-------Before-------)
    BRAND CUST JAN2002 FEB2002
    br1 c1 200 300
    br1 c2 5 30
    br2 c3 999 444

    (-------After-------)
    BRAND CUST DATE QUANTITY
    br1 c1 jan2002 200
    br1 c1 feb2002 300
    br1 c2 jan2002 5
    br1 c2 feb2002 30
    br2 c3 jan2002 999
    br2 c3 feb2002 444
    Last edited by pankajpatel01; 10-10-02 at 14:47.

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Create your new table structure (copy old table without data and change fields)

    INSERT INTO NewTable (BRAND, CUST, DATE, QUANTITY)
    SELECT OldTable.BRAND OldTable.CUST, "Jan2002" AS MonthYr, OldTable.JAN2002
    FROM OldTable;

    run this query 12 times if you have 12 months of data.
    prior to each new run - you will need to change the following:
    "Jan2002" AS MonthYr --> "Feb2002" AS MonthYr
    OldTable.JAN2002 --> OldTable.Feb2002

    Shouldnt take more than a few min.

    Matt

  3. #3
    Join Date
    Oct 2002
    Posts
    6
    Thank you for the suggestion. I need to elaborate on what I am trying to do.

    The columns eg, Jan2003, Feb2003 are of variable month ranges, they are not always 12. Sometimes I receive more than 12 months of information.

    Is there a way to automate the suggested process? Or is there a more convenient process?

    I was thinking of doing something like columncount-2, and looping that many times with an append statement. With a dataset of 96000rows and approx 12months of data, my idea will take plenty of time.



    Any suggestions to speed the process would be appreciated

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Try this:


    Dim MyDB As DAO.Database
    Dim MyTableDef As DAO.TableDef
    Dim i As Integer
    Dim strSQL As String
    Dim strFieldName As String

    Set MyDB = CurrentDb
    Set MyTableDef = MyDB("OldTable")
    'loop through the TableDef collection looking for months
    For i = 0 To MyTableDef.Fields.Count - 1
    Select Case Left(MyTableDef.Fields(i).Name, 3)
    Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec":
    strFieldName = MyTableDef.Fields(i).Name
    Case Else:
    strFieldName = ""
    End Select
    'now build and run the append query
    strSQL = "INSERT INTO NewTable (BRAND, CUST, DATE, QUANTITY)" & _
    " " & _
    "SELECT OldTable.BRAND OldTable.CUST, '" & strFieldName & "' AS " & _
    "MonthYr, OldTable." & strFieldName & _
    " FROM OldTable;"
    If strFieldName <> "" Then DoCmd.RunSQL strSQL
    Next i
    Last edited by Rockey; 10-10-02 at 17:46.

  5. #5
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Wait a minute, can this SQL works?

    Code:
    (-------Before-------)
    BRAND 	CUST	JAN2002 FEB2002
    br1 	c1 	200 	300
    br1 	c2	5 	30
    br2 	c3	999 	444
    
    (-------After-------)
    BRAND 	CUST 	DATE 	QUANTITY
    br1 	c1 	jan2002 200
    br1 	c1 	feb2002 300
    br1	c2 	jan2002 5
    br1 	c2 	feb2002 30
    br2 	c3 	jan2002 999
    br2 	c3 	feb2002 444
    
    SELECT BRAND, CUST, "jan2002" AS DATE, JAN2002 AS QTY FROM Table1 UNION
    SELECT BRAND, CUST, "fev2002" AS DATE, FEV2002 AS QTY FROM Table1 UNION
    SELECT ...
    You'll have to use many UNION statement thought. It is not creating any table, it is just a SELECT statement. You can, however, build that SQL in code like Rockey shows, by brosing for the field in the table, but I suppose there is only 12 months and that only the year migth change, so you could just create the query one time and only changing the "2002" value

    JefB - hope it helps

Posting Permissions

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