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/f...sts.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
Flecal wrote:
> 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?
Similar Threads
