Results 1 to 3 of 3
  1. #1
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43

    Thumbs down concatenate multiple record field values into a single field

    I'm struggling with this, essentially what I want to do is this:



    In a table of items and program no's there may be multiple records that have different program no's because they are made on different machines

    Item Prog
    1234 1234P1
    1234 1234P2
    1234 1234P3

    In a report for a single Item I want to concatenate the three possible program No's into a single field 1234P1,1234P2, 1234P3

    Any ideas please

    Thanks

    Ian

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review http://allenbrowne.com/func-concat.html

    How many possible programs for each item? Maybe a CROSSTAB:

    TRANSFORM First(Table1.Prog) AS FirstOfProg
    SELECT Table1.Item
    FROM Table1
    GROUP BY Table1.Item
    PIVOT Right([Prog],2) IN (P1,P2,P3);

    Or this version:

    TRANSFORM First(Table1.Prog) AS FirstOfProg
    SELECT Table1.Item
    FROM Table1
    GROUP BY Table1.Item
    PIVOT DCount("*","Table1","Item='" & [Item] & "' AND Prog<'" & [Prog] & "'")+1;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tuniwes is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    43
    Apologies for lengthy reply time, currently off work after having a new knee

    The function by Allen Browne that you guided me to, did exactly what I wanted to do.

    Thanks so much for your help

    Very much appreciated

    Best

    Ian

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Concatenate Multiple Rows into one field
    By shank in forum Access
    Replies: 4
    Last Post: 01-17-2019, 09:08 AM
  2. Replies: 8
    Last Post: 06-14-2018, 10:14 AM
  3. Multiple values for a single database field
    By NJH0512 in forum Access
    Replies: 3
    Last Post: 01-30-2018, 07:31 PM
  4. Retrieving Multiple Values into a Single Field
    By Simbiose in forum Queries
    Replies: 6
    Last Post: 08-23-2016, 07:54 AM
  5. Replies: 5
    Last Post: 08-29-2011, 05:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums