Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Unanswered: Combining Multiple Values into One Field

    I have two tables called tblName and tblType.

    tblName
    Name Type
    Mike 1
    Frank 2
    Robert 2
    Edward 3
    David 4
    Albert 4

    I want the output to look like this in the tblType table:

    tblType
    Type Name
    1 Mike
    2 Frank, Robert
    3 Edward
    4 David, Albert
    5

    When I execute the following vb code all I get is the last value from the tblName table:

    strSQL = "UPDATE tblType "
    strSQL = strSQL & "SET tblType.Name = tblType.Name + " ', ' + "
    strSQL = strSQL & "tblName.Name "
    strSQL = strSQL & "FROM tblName, tblType "
    strSQL = strSQL & "WHERE tblName.type = tblType.type;"


    Any suggestions?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  3. #3
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225
    Thanks Rudy, I knew there was some way of accomplishing this. I went to the link you posted and gave the COALESCE Function a quick read but is there a way of substituting the comma for another value such as a vbCRLF?

    Thanks,

    Mark

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is there a way of substituting the comma for another value such as a vbCRLF
    don't see why not -- didja try it?

  5. #5
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Thanks Rudy

    Rudy,
    Thanks for your post. I was unable to make the query do what I initally wanted it to do. I was able to accomplish what I wanted by using two nested recordsets. It takes a little longer to complete but it does what I want it to do.


    Thanks!

    Mark

Posting Permissions

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