combine info into

[Flecal]

I have many rows of names. The data is sorted out into the following
columns: First, Last, Address, City, State, Zip.

John Doe 1234 E Street Los Angeles CA 12345
Jane Doe 1234 E Street Los Angeles CA 12345
Bob Smith 4321 Tree Ave Denver CO 54321
Joe Johnson 987 North Lane Philadelphia PA 65432
Sue Johnson 987 North Lane Philadelphia PA 65432
Lisa Johnson 987 North Lane Philadelphia PA 65432

I want to combine John & Jane Doe into one row, Joe, Sue & Lisa Johnson into
one row, etc.

what can I do to look at the addresses, see if they're the same, look at
last names, see if they're the same, then combine first names into one row
with the appropriate last name and address info?

 

[John Spencer]

Query one: Identify the unique values

SELECT Last, Address, City, State, ZIP
FROM [YourTable]
GROUP BY Last, Address, City, State, ZIP

Query Two - uses a VBA concatenate function - see
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
You will need to copy the function and save it to a VBA module in your database.

SELECT Last, Address, City, State, ZIP
, Concatenate("SELECT First FROM [YourTable] WHERE Last=""" & Last & """ AND
Address = """ & Address & """ AND City = """ & City & """ AND ZIP = """ & ZIP
& """", ",")
FROM [QueryOne]

Also, you will need to handle changing the last comma to an ampersand. Or you
could delimit all the names with an ampersand instead of a comma. So you end
up with
Joe & Sue & Lisa instead of Joe, Sue & Lisa.

Additionally, you might want to add an order by clause to the query being used
by the concatenate function. But with the data given the only thing you could
do would be to return the names in alphabetic order.

If you are creating a report, you might want to base the report on queryOne
and assign the first names to an unbound control using VBA and the concatenate
function to create the first names value.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County