How to put multiple values in one field

  • Thread starter Thread starter Nightmoon Eagle
  • Start date Start date
N

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,

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
 
Back
Top