Results 1 to 5 of 5

How to Combine some Records to one row by one field

  1. #1
    romi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    7

    How to Combine some Records to one row by one field

    Dear All
    I have tabel employee:


    ID Letter No Employee Name Employee No
    1 223 Jack 01
    2 223 Allan 02
    3 223 Smith 03
    4 224 Jack 01
    5 224 Rudy 04
    6 225 Ivan 05



    and I want to Combine some records with the same of letter No into one row..
    how to do like table below..?
    Thank You..

    Letter No Employee Name1 Employee No1 Employee Name2 Employee No2 Employee Name3 Employee No3
    223 Jack 01 Allan 02 Smith 03
    224 Jack 01 Rudy 04
    225 Ivan 05

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is a crosstab query - actually two queries, one for the name and another for the number which you would then use UNION to combine both fields. Use the wizard to create the query for you.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,968
    Review http://allenbrowne.com/ser-67.html

    Need another field that has a sequence identifier for each LetterNo group.

    It would be easier if employee name and number combined as a concatenated output which would display something like: 04 : Rudy or Rudy : 04.

    TRANSFORM First([EmployeeName] & " : " & [EmployeeNo]) AS Name_No
    SELECT Employees.LetterNo
    FROM Employees
    GROUP BY Employees.LetterNo
    PIVOT DCount("*","Employees","LetterNo=" & [LetterNo] & " AND ID<" & [ID])+1;

    LetterNo 1 2 3
    223 Jack : 01 Allan : 02 Smith : 03
    224 Jack : 01 Rudy : 04
    225 Ivan : 05



    Note the elimination of spaces from field names.


    Another approach using VBA http://allenbrowne.com/func-concat.html.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  4. #4
    romi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    7
    Ayetee and June
    My Goal is to make report from transpose that tabel so i get repot group by : Letter No. (one page report per letter No.)

    Page1 (Letter No : 223)
    1. Employe Name : Jack
    Employee No : 01
    2. Employe Name : Allan
    Employee No : 02
    3. Employe Name : Smith
    Employee No : 03

    Page2: (Letter No : 224)
    1. Employe Name : Jack
    Employee No : 01
    2. Employe Name : Rudy
    Employee No : 04

    Page3: (Letter No : 225)
    1. Employe Name : Ivan
    Employee No : 05

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,968
    Transposing data won't produce that output.

    Use report Sorting & Grouping features. Set for grouping by LetterNo and ForceNewPage property set to yes.

    Use textbox RunningSum property to provide the group sequence number.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

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

Similar Threads

  1. Combine records in field
    By pbDudley in forum Queries
    Replies: 2
    Last Post: 05-08-2017, 07:02 AM
  2. Combine records into one row
    By Back2Basics in forum Queries
    Replies: 3
    Last Post: 03-10-2017, 02:36 AM
  3. Replies: 1
    Last Post: 04-06-2016, 08:26 AM
  4. Combine several records into one record
    By Helge in forum Queries
    Replies: 9
    Last Post: 01-25-2014, 02:26 PM
  5. Replies: 3
    Last Post: 02-19-2013, 07:15 AM

Posting Permissions

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