I have a table with addresses and names. I can easily sort it by address to see all the people living at the same address grouped together. However, this is problematic with mail-merges, since I wind up generating a new letter for each person, sending multiple mailings to the same residence.

Here is what the table looks like:

Name Address
===== ==========
Mary 101 Franklin Ave
John 101 Franklin Ave
Andrew 101 Franklin Ave
Matthew 101 Franklin Ave
Tim 2 Main St
Mark 2 Main St
Sam 2 Main St

Is there a way I can use a query combine all the names into a single "virtual" field, so that I only have one row for each address?

Here is what I would like to see:

Name Address
============= =================
Mary, John, Andrew, Matthew 101 Franklin Ave
Tim, Mark, Sam 2 Main St

Are you using an Access Database or what?

Yes, I am using Access 2003.

There is no way to do this with just a query. What you will need to do is loop through a sorted recordset and concatenate the first names into a temporary table.

There is an issue of your database design. You should have at least 2 tables here; A table for households which has the address info and a table for household members which has the name info. That would make it easier.

I also found a possible solution for you here:

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=82&Number=1047063&Forum=,All_Forums,&Words=&Searchpage=0&Limit=25&Main=1046831&Search=true&where=&Name=&daterange=&newerval=&newertype=&olderval=&oldertype=#Post1047063&bodyprev=

This looks like a promising solution. However, I managed to accomplish my task using Update and Append queries. It is a little intimidating to me to use VB code.

Thanks for the help.

VBA isn't that hard and you miss a great deal of the power of Access if you don't use it.

You should also spend time researching normalization rules. Normalization is an absolutely essential process for designing databases. Normalized databases are more efficient, easier to use and easier to enter data into. The rules of normalization have existed for over 30 years so they have stood the test of time as nothing as come along to replace them.