If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
How to put multiple values in one field
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 |
#3
|
|||
|
|||
How to put multiple values in one field
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 |
#4
|
|||
|
|||
How to put multiple values in one field
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 |
Thread Tools | |
Display Modes | |
|
|