Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: column to row conversion

    Hello,

    I am new to Access and need some help. I have data which looks like this:

    ID Jan Feb March April May
    1 100 101 102 103 104
    2 200 201 202 203 204
    3 300 301 302 303 304
    ...
    for the year 2010

    I need to convert it to the following:

    ID Year Month Value
    1 2010 1 100
    1 2010 2 101
    1 2010 3 102
    1 2010 4 103
    1 2010 5 104
    2 2010 1 200
    2 2010 2 201
    2 2010 3 202
    2 2010 4 203
    2 2010 5 204
    ...

    How can I do this using Access 2010?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well you could use an insert Query, or 12 of, one for each month
    eg
    insert into myTable (ID,Year,Month, Value) select ID,"2010",1,Jan from mysourcetable
    insert into myTable (ID,Year,Month, Value) select ID,"2010",2,Feb from mysourcetable
    ...
    insert into myTable (ID,Year,Month, Value) select ID,"2010",12,Dec from mysourcetable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    3
    Thanks a lot for the reply!

    But the thing is that I have a very big dataset (almost a 100 thousand entries/IDs), so it will be very tedious to write the code, one for each month for each ID, the way you have described. Is there a way to address this issue?

    Thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    but you don't need to do it for each ID, thats the whole point of the query as proposed, yes you do need to do it for every month and you will need to be careful when editing.....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    3
    I ran the code, but its saying that I have a syntax error in the FORM clause

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not to sure I can help you on that matey

    either post your SQL here
    or work it out for yourself

    failing that you will have to allow us time to dust down our crystal balls to deduce what you may or may not have done

    I'm presuming your
    I ran the code, but its saying that I have a syntax error in the FORM clause
    is a typo when posted here.

    if you do want to keep details of your SQL secret then this may be a good source for you
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    failing that, there's always http://www.google.co.uk/#hl=en&sclie...w=1920&bih=995
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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