How to put multiple values in one field

For those with access to an SQL Server, the query works in SQL Server 2000,
2005, and 2005 EXPRESS and is provided below. For those without SQL Server, I
will try to explain my dilemma.

I have a table, tblA which contains locations and the name of the owner and
inhabitants. Each inhabitant/owner has a seperate record in the database with
the same location.

i.e.
Location 1, InhabitantA
Location 2, InhabitantB
Location 2, InhabitantC
Location 3, OwnerA
Location 3, InhabitantD

I want to create a query that shows:
Location 1, InhabitantA
Location 2, InhabitantB InhabitantC
Location 3, OwnerA InhabitantD

or

Field1, Record1.Field2 Record2.Field2 Record3.Field2

Is this possible in Access?

I know it works in SQL as shown by the query below:

SELECT TOP 3 Location, Surnames = replace ((SELECT Surname AS [data()]
FROM tblA WHERE Location = a.Location ORDER BY Surname FOR xml path('')), '
', char(10))
FROM tblA a
WHERE Location IS NOT NULL
GROUP BY Location

Thanks in advance.
NmE

In Access, we generally call a VBA function that concatenates the related
records and returns them as a string.

This kind of thing:
http://allenbrowne.com/func-concat.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nightmoon Eagle" wrote in
message
...
For those with access to an SQL Server, the query works in SQL Server
2000,
2005, and 2005 EXPRESS and is provided below. For those without SQL
Server, I
will try to explain my dilemma.

I have a table, tblA which contains locations and the name of the owner
and
inhabitants. Each inhabitant/owner has a seperate record in the database
with
the same location.

i.e.
Location 1, InhabitantA
Location 2, InhabitantB
Location 2, InhabitantC
Location 3, OwnerA
Location 3, InhabitantD

I want to create a query that shows:
Location 1, InhabitantA
Location 2, InhabitantB InhabitantC
Location 3, OwnerA InhabitantD

or

Field1, Record1.Field2 Record2.Field2 Record3.Field2

Is this possible in Access?

I know it works in SQL as shown by the query below:

SELECT TOP 3 Location, Surnames = replace ((SELECT Surname AS [data()]
FROM tblA WHERE Location = a.Location ORDER BY Surname FOR xml path('')),
'
', char(10))
FROM tblA a
WHERE Location IS NOT NULL
GROUP BY Location

Thanks in advance.
NmE

Allen,

Thank you for the code. I do have a problem with it, though. When using it
in a query, I keep getting the error:

Error 3061: Too few parameters. Expected 1.

the query I have is:

SELECT Location, X, Y, Rotation, TextHeight, ConcatRelated("surname",
"PlotPoints", "Location = " & [Location], [Location], "char(10)") AS Surnames
FROM PlotPoints;

NmE

Fixed the problems.

Thanks again for the code, Allen.

NmE

"Nightmoon Eagle" wrote:

Allen,

Thank you for the code. I do have a problem with it, though. When using it
in a query, I keep getting the error:

Error 3061: Too few parameters. Expected 1.

the query I have is:

SELECT Location, X, Y, Rotation, TextHeight, ConcatRelated("surname",
"PlotPoints", "Location = " & [Location], [Location], "char(10)") AS Surnames
FROM PlotPoints;

NmE