Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    68

    Unanswered: From Column Wise data to Row(transactional) Wise data

    I have a situation where the revenue is set up in individual columns named for the month and year..

    Before Change

    Product Jan 02 Feb 02 Mar 02
    XYZ $10 $20 $30

    After Change

    Month Year Product Amt
    Jan 2002 XYZ $10
    Feb 2002 XYZ $20

    Anyone have a code snipit or something set up to do this very easily.
    Thanks
    jm

  2. #2
    Join Date
    Jun 2004
    Posts
    16
    Great your normalizing your data.

    First create a table "tblNormalized" with the fields:

    ID Autonumber (PK)
    Product Text
    Date date
    Amount Currency

    then you are going to have to create an append query for each Month field. Below is the SQL for the data from Jan 2002:

    INSERT INTO tblNormalized ( Product, Amount, [Date] )
    SELECT Table1.Product, Table1.[Feb 02], 1/1/2002 AS [Date]
    FROM Table1;

    repeat for each month

    You write an alias for each month. I would use a date such as 1/1/2002 for Jan - 02 as you can easily pull out the month and year in a query.

  3. #3
    Join Date
    Aug 2003
    Posts
    68
    Thanks thats what I Started to do. Was hoping there was an easier way..
    jm

Posting Permissions

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