Results 1 to 4 of 4

Thread: Merging fields

  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Question Unanswered: Merging fields

    I have an Access 2007 table with two fields, ProductName and ProductSort:


    ProductName / ProductSort
    juice / cool
    juice / tropical
    juice / natural
    cola / cool
    cola / artificial

    And I want it to become like this:

    ProductSum
    juice, cool, tropical, natural
    cola, cool, artificial
    ..etc

    In other words, the ProductName field should be merged with the ProductSort field to a new textfield called ProductSum with ProductName and all related ProductSorts separated with commas (one uniqe product per row).

    Is this possible by using a Query? If not how?

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Merging Fields

    You can solve this problem with the following Code:
    Code:
    Public Function Product_Sum()
    Dim bkName As String, bkSort As String
    Dim dtName As String, dtSort As String, strJoin As String
    Dim db As Database, rstin As Recordset, rstout As Recordset
    
    Set db = CurrentDb
    Set rstin = db.OpenRecordset("Table_1", dbOpenDynaset)
    Set rstout = db.OpenRecordset("Table_2", dbOpenDynaset)
    
        dtName = rstin![ProductName]
        bkName = dtName
    Do While Not rstin.EOF
        strJoin = dtName
        Do While dtName = bkName And Not rstin.EOF
            strJoin = strJoin & "," & rstin![ProductSort]
            rstin.MoveNext
            If Not rstin.EOF Then
               dtName = rstin![ProductName]
            End If
        Loop
        rstout.AddNew
        rstout![ProductName] = bkName
        rstout![Productsum] = strJoin
        rstout.Update
        bkName = dtName
    Loop
    rstin.Close
    rstout.Close
    
    Set rstin = Nothing
    Set rstout = Nothing
    Set db = Nothing
    
    End Function
    Index Table_1 on ProductName Field before running the Code. You need a second Table (Table_2) with the same structure as Table_1 with one more Field: ProductSum.

  3. #3
    Join Date
    Dec 2009
    Posts
    9

    Another way?

    Thank you for the code though I feel uncomfortable with going into the VB code... Could this be a way to solve the problem also?

    From this table:

    ProductName / ProductSort
    juice / cool
    juice / tropical
    juice / natural
    cola / cool
    cola / artificial

    To a new table:

    ProductName / ProductSort1 / ProductSort2 / ProductSort3 / [etc...]
    juice / cool / tropical / natural
    cola / cool / artificial

    And then concat the fields with a "&" sign to a new table. Or am I out in the ozon here...

  4. #4
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    Merging Fields

    You can do this with the help of PIVOT Table. For that you must create one more Field (ID) and must give sequence number for each group of ProductSort Items.

    Sample Table Datasheet View & PIVOT Table Form View images are attached.

    1. Select the Table
    2. Select Form from Insert Menu and select Auto Form:PIVOT Table from the displayed List.
    3. Drag and Drop Product Name in the Drop Row Panel in the PIVOT Table Design View.
    4. Drag and Drop ID Field on the Column Fields Panel.
    5. Drag and Drop Product Sort Field in the Main Window.
    Attached Thumbnails Attached Thumbnails table_1.jpg   PIVOT_TableForm.jpg  
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

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