Results 1 to 10 of 10
  1. #1
    shanmugamgsn is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    17

    I need similar output of wm_concat function that is in Oracle

    Hi,

    I have a table ,

    A B C M


    1 2 3 hai
    1 2 3 hai
    1 2 3 bye
    1 2 3 sad
    2 3 4 hai
    2 3 4 bye
    5 6 7 tin
    1 2 3 bat
    I expect an ouput as follows,

    A B C M
    1 2 3 hai,hai,bye,sad,bat
    2 3 4 hai,bye
    5 6 7 tin
    Is this possible in MS access using query or VBA function or any how?


    I'm literaly trying this for more than two days.

    I found a solution in oracle using wm_concat function but my bad luck i'm using Ms access, i cannot find any function or solution for above..



  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    shanmugamgsn is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    17
    Thanks for reply Orange.
    but i'm getting as follows

    Query4abcExpr1003123hai,hai,bye,sad,bat,tin123hai,hai,bye,sad,bat,tin123hai,hai,bye,sad,bat,tin123hai,hai,bye,sad,bat,tin123hai,hai,bye,sad,bat,tin123hai,hai,bye,sad,bat,tin234hai,hai,bye,sad,bat,tin

    I dont want this. I need like in a single line of removing duplicates

    A B C M
    1 2 3 hai,hai,bye,sad,bat
    2 3 4 hai,bye
    5 6 7 tin

    Can i accomplish it??

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show your table layout, and the query (sql) in which you used the function.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I just ran the function with 2 of my tables without issues.

    My tables are concat2.jpg and concat3.jpg
    The query set up is concat0.jpg and result is concat1.jpg

  6. #6
    shanmugamgsn is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    17

    pls check out my result where i'm doing mistake

    Hi Orange,

    Check my table,query and result...
    I also used the same as you did but why i'm not getting output???
    Please help me out orange


    Attachment 5162

    Attachment 5163

    Attachment 5164

    Attachment 5165

  7. #7
    shanmugamgsn is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    17

    got it Orange. but still small query

    Orange i got it.
    I made mistake by not giving the condition as third parameter.

    But i have one more doubt, this third parameter accepts only number a?
    coz it throws error when i gave "a =[a]" coz a is Text datatype..
    I'm getting error "Error : 3464 data type mismatch in criteria expression in access "

    I want to do this concat function for tex?

    One more thing can i have three text condtions at a time ? if so how can i give in this function?

    Attachment 5166
    Last edited by shanmugamgsn; 11-14-2011 at 01:06 AM. Reason: actual problem now is text

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's an example

    My Query

    SELECT DISTINCT Customers.Country, Concatrelated("City","Customers","Country='" & [Country] & "'") AS ConcatCity
    FROM Customers;
    The Customers is a table from the Northwind database sample.

    The output is shown in concat4.jpg

  9. #9
    shanmugamgsn is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2011
    Posts
    17
    Orange ur genius dude....

    Thanks dude.. u helped me a lot....

  10. #10
    Rianne is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Canada - Picture Butte
    Posts
    1
    Thanks! You have me helped too!

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

Similar Threads

  1. ODBC with Oracle
    By asearle in forum Queries
    Replies: 1
    Last Post: 09-07-2011, 10:22 PM
  2. Oracle to MSACCESS
    By karankukreja in forum Access
    Replies: 1
    Last Post: 03-24-2011, 08:20 AM
  3. Oracle LAG Equivalent
    By OzzyMiner in forum Queries
    Replies: 2
    Last Post: 03-10-2011, 11:41 AM
  4. Converting access to oracle
    By wthoffman in forum Access
    Replies: 0
    Last Post: 03-08-2011, 11:05 AM
  5. Oracle password
    By thart21 in forum Programming
    Replies: 0
    Last Post: 04-06-2010, 10:12 AM

Tags for this Thread

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