Cered
asked on
Combine values from related rows into a single cell
I am trying to pull data from a access database. I then want to take this data and send it out in a mail merged email. I am using a table that has the following:
Name address class
Bugs bunny BBunny@me.com Rabbit
Bugs bunny BBunny@me.com carrot eater
Bugs bunny BBunny@me.com Hole digger
Bugs bunny BBunny@me.com Varmit
Daffy Duck DDuck@me.com Duck
Daffy Duck DDuck@me.com Space Duck
what I'd like it sto be able to combine all of the class flield into one so that each character has only one line. Like this:
Name address class
Bugs bunny BBunny@me.com Rabbit, carrot eater, Hole digger, Varmit
Daffy Duck DDuck@me.com Duck, Space Duck
This would allow me to send only 1 email per name, not the 4 for bugs bunny and 2 for daffy duck. (all the names and email address have been changed....)
Appricate any help I can get.
Name address class
Bugs bunny BBunny@me.com Rabbit
Bugs bunny BBunny@me.com carrot eater
Bugs bunny BBunny@me.com Hole digger
Bugs bunny BBunny@me.com Varmit
Daffy Duck DDuck@me.com Duck
Daffy Duck DDuck@me.com Space Duck
what I'd like it sto be able to combine all of the class flield into one so that each character has only one line. Like this:
Name address class
Bugs bunny BBunny@me.com Rabbit, carrot eater, Hole digger, Varmit
Daffy Duck DDuck@me.com Duck, Space Duck
This would allow me to send only 1 email per name, not the 4 for bugs bunny and 2 for daffy duck. (all the names and email address have been changed....)
Appricate any help I can get.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are using a mail merge, you have no option but what Dale suggested. However, if you are using OLE automation, the possibilities are expanded. Here are three samples. The first is a letter with a simple list, the second is a sample with all the programmed tables and the third is a sample with one of the tables.
ASKER
I'm adfraid i am much of an access guy - where do i run the code from?
What Dale posted is a function so you would add it in your query.
The URL I posted provides an example of how to write the SQL string and the syntax for calling the function properly.
ASKER
not sure where i "would add it to my query"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Cered
My apologies. I didn't look at the Access tag and assumed that your data is in an Excel File, that's why I offered that solution which is clearly irrelevant. :(
My apologies. I didn't look at the Access tag and assumed that your data is in an Excel File, that's why I offered that solution which is clearly irrelevant. :(
ASKER
no problem Subodh - it was in excel and then i moved it into access. appricate the help!
You're welcome Cered! Glad we could help.
http://theaccessweb.com/modules/mdl0008.htm