How to put multiple values in one field

[Nightmoon Eagle]

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 Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Nightmoon Eagle]

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

 

[Nightmoon Eagle]

Fixed the problems.

Thanks again for the code, Allen.

NmE