Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2006
    Posts
    11

    Question Unanswered: Concatenate values of fields WHERE names are equal

    MS Access 2000

    I have a table with a list of file shares and the data 'owner' for that file share. One file share along with the owner's info (email, etc) is stored in each record;
    I need to send emails out to each owner with a list of all file shares that 'belong' to that owner in a list.
    I have a product to automate the emails, but it processes them one record at a time.
    Is there a way to calculate a long string of each file share name with carriage returns in between each one in a memo field based on equal owner names?

    Thanks - it's been ages since I had to work on databases and I'm extremely rusty!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Not likely with that structure. What happens if the owners name is mis-spelled on one record or the puncuation is different?

    If you had another table for owners and then related that table to your file share table, then you could probably pull it off.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    11
    Thanks for looking at it. If I separated the data, how would you recommend relating the records?

  4. #4
    Join Date
    Feb 2006
    Posts
    11
    I asked because I actually do have the user information in another table, I just can't figure out how to get all the file shares grouped together into one field. I need to do this so that I can send each user an email with the list of file shares assigned to them. Thanks again for any suggestions!

  5. #5
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    If you have "client information" in one table and "file share" in another then just make a 1-many relationship between the 2 tables (1 client - many file shares, create a form Main form (Client) subForm (FileShare) , create a report that will show 1 client with many fileshares , e-mail the report to client.

    Result client get e-mail with report attached with a list of his/hers "file share"
    Regards,



    John A

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The usual wheeze for this sort of thing is to create a function that takes the users ID as a parameter. Open a recordset for all the files for this user and loop through them, concatenating them into a string. Spit out the string when the recordset reaches the end of the file.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Posts
    11

    Red face create a function

    "create a function that takes the users ID as a parameter. Open a recordset for all the files for this user and loop through them, concatenating them into a string. Spit out the string when the recordset reaches the end of the file."

    Could you help with a code example for that? Using Access 2000... I've tried several examples from the web and can't get it to work.



    I am able to get the information together on a form, but need it in the form of a query for use by another program we're using.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi turnertodd

    How are you at setting up a public function that accepts arguments and returns a string?

    ...and how good are you at using recordsets (ADO or DAO)?

    It will just help me help you...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by turnertodd
    I've tried several examples from the web and can't get it to work.
    Even better - waddaya got so far?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2006
    Posts
    11
    I'm a complete novice - worked with some simple procedures 10+ years ago and now don't remember squat! I was trying to modify "‘Concatenate fields in same table" by Dev Ashish found on the web but I know barely enough to be dangerous. Thanks again.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Jolly good.

    Dev's code for this is very generic - as such it is probably more complex for you to see what is going on.

    Attached is pretty well the same but more stuff hard coded, no error trap etc. Easier to adapt I would expect.
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2006
    Posts
    11
    Thanks very much! I'm still having a problem though - My ID field is a string rather than an Integer so I'm getting a run time error "expected 1" if I change that to "as string" in the code vs. "as integer". When left "as integer" the formula returns #error....

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    New version attached. Includes string version and annotates the code.

    HTH
    Attached Files Attached Files
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2006
    Posts
    11
    You're a gem! Thanks VERY much! Works like a charm.

Posting Permissions

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