Access query one column results into new column

[tpohara]

I am using Microsoft Access 2003

I have a database table with names in it (Firstname, Lastname).

I would like to combine two results(from one table) in one row. I cannot understand how to get Access 32003 to do so.

Sample data:
First name Last name
Fred Jones
Wilma Jones

I require a row like

First Name Last Name First names
Fred Jones Fred, Wilma
Wilma Jones Fred, Wilma

The ultimate purpose of the exercise is to print labels with combined addresses.

Thanks for any assistance.

 

[tpohara]

I should also say that I have attemptd to write a Module to do this trying the following technique:

Concats (Module name);

'Concats returns a list of the Last Names
'so far found for the current Last Names.
Public Function Concats(strLast_Name As String, _
strFirst_Name As String) As String
Static strLastLast_Name As String
Static strFirst_Names As String

If strLast_Name = strLastLast_Name Then
strFirst_Names = strFirst_Names & ", " & strFirst_Name
Else
strLastLast_Name = strLast_Name
strFirst_Names = strFirst_Name
End If
Concats = strFirst_Names
End Function


Then I call this Module from a Query

SELECT [Address Data].[Last Name], Max(Concats([Address Data].[Last Name],[Address Data].[First Name])) AS First_Names
FROM [Address Data]
GROUP BY [Address Data].[Last Name];

I constantly get the following error:

undefined function 'Concats' in expression

Humm , thanks again

 

[xenou]

It's an awfully tough one. You might try joining the table to itself on matching addresses to get a subset of all names with the same addresses. Let's say there's 20 names with the same address (10 pairs of individuals) - output these names together with their shared address by concatenating the names and using the address values from only one of them. The table would be joined to itself on address = address, or city = city, postal code = postal code, and so on.

A temp table would not hurt here to ease this process by getting the dupes out by themselves as the first step. The remaining names (with no duplicates) can be taken as they are.