Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Glasgow
    Posts
    2

    Lightbulb Unanswered: Some Genius - Help Please!!

    I was hoping someone could help with this query.

    I have a table that contains duplicate rows of customers. However each row has three unique columns at the end of the row.

    I need to create new columns on the end of each row that will shift this data all on to the one row.

    for example :

    Contact number, Name, Spend1, Spend 2, spend3
    123, John, 5,6,7
    123, John, 8,9,10
    123,John,11,12,13

    Change to :

    Contact number, Name, Spend1, Spend 2, spend3, Spend4, Spend5, spend6, Spend7, Spend 8, spend9
    123, John, 5,6,7,8,9,10,11,12,13

    Therefoe I need to create 7 new columns that add this data to the one row.

    Can anyone help ??

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    1) are you shure that you need a new table? if not, you can use crostable view.

    2) what if you have more than 3 dublicate rows, than 6 additional columns won't be enough.
    anyway you can't fill this columns only via query, you need coding.
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Location
    Glasgow
    Posts
    2
    Thanks a lot for replying.

    I need to export the data for a mailing file, thats why I need it as a table.

    Some customers will only have one row, and the maximum is eight.

    I thought there might be a bit of code I could use as a function to create the new columns. This would lift each unique sales data and add it to the first row. I could then de-dupe the customers on contact number leaving me with one row for each customer and all the unique sales data on the same row.

    If you know what function to use that would be great, if not don't worry I really apprecaite your help.

    Thanks,

  4. #4
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Is this access data or SQL data? If it's SQL, create a cursor.
    Cathy

Posting Permissions

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