Results 1 to 7 of 7
  1. #1
    Ramk is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    3

    Merge rows with same value and having different columns

    Please find the attachment.


    I need to merge rows with different columns.


    Actual Data

    CrId WO Date AppID Status
    CR1 wo1 12/1/2020 experian closed
    CR1 wo2 12/1/2020 experian closed
    CR2 wo3 12/2/2020 Photoshop closed
    CR2 wo4 12/2/2020 Photoshop closed
    CR2 wo5 12/2/2020 Photoshop closed
    CR3 wo6 12/3/2020 MsOffice closed
    CR3 wo7 12/3/2020 MsOffice closed
    CR3 wo8 12/3/2020 MsOffice closed
    CR3 wo9 12/3/2020 MsOffice closed



    Desried Result

    CrId WO Date AppID Status
    CR1 wo1,wo2 12/1/2020 experian closed
    CR2 wo3,wo4,wo512/2/2020 Photoshop closed
    CR3 wo6,wo7,wo8,wo9 12/3/2020 MsOffice

    Please help to how write query to get result as above

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you could use a Crosstab query.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Ramk is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    3
    Tried no luck.. CRId, date, AppID and status should merge and WO field should concatenate.
    If possible can you please help with query code

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by Ramk View Post
    Tried no luck.. CRId, date, AppID and status should merge and WO field should concatenate.
    If possible can you please help with query code
    For concatenation I have always used Allen Browne's code
    http://allenbrowne.com/func-concat.html

    thDBguy also has a similar function
    http://www.accessmvp.com/thedbguy/co...itle=simplecsv

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Ramk is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    3
    Thank you, but I'm not allowed to do VBA, can we do with normal query

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by Ramk View Post
    Thank you, but I'm not allowed to do VBA, can we do with normal query
    Not that I am aware of.?

    Ask the teacher to show you the way?, then come back here and tell us how it was done.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you've not tried hard enough or your example data does not represent your reality

    use a crosstab - CRID, Date, AppID and status as row headings, WO as column heading and value(first) , then a second query to concatenate the results as required

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

Similar Threads

  1. Replies: 22
    Last Post: 12-20-2015, 02:46 AM
  2. help merge rows in query
    By pika2112 in forum Access
    Replies: 1
    Last Post: 12-10-2014, 10:24 AM
  3. Replies: 4
    Last Post: 02-25-2014, 01:09 AM
  4. Replies: 12
    Last Post: 07-31-2013, 01:29 PM
  5. merge or concatenate two rows in one
    By vojinb in forum Queries
    Replies: 7
    Last Post: 08-03-2011, 09: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
  •  
Other Forums: Microsoft Office Forums