• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Show multiple records in one field using SQL

I'm running an Access front end and a SQL backend.  I have tblDaily and tblRooms.  There is a one to many relationship.  The fields in tblDaily can have multiple rooms.  I want to display 3 fields (Facility, Elevation and Area) from tblDaily but also the multiple rooms from tblRooms all in one field on a report.
Ex: Facility, Elevation, Area, Rm1, Rm2, Rm3

I found Function fncConcat() example code on Expert Exchange but it doesn't work for me because my backend is SQL Server.  Can anyone offer any other suggestions?  Please?
  • 2
1 Solution
I am not sure wether this will work, but give this a try

select facility, elevation, area, (select concat(rooms) from tblRooms where FID_Daily = tblDaily.id) from tblDaily

(Dont know what your tblRooms looks like, i assumed it has a rooms field and a foreign key called FID_Daily... of course you will have to replace it with real fieldnames)
BobRosasAuthor Commented:
I could really use more help with the SQL statement.  I forgot to include tblTestPerformed which links tblDaily and tblRooms.  Also tblWorkRelease has the field “facility” which I need.  
The following is the SQL code I’m trying but I get the error “Syntax error in query expression ‘(select concat (tblRooms.RoomNo))’.  

SELECT tblWorkRelease.Facility, tblDaily.Elevation, tblDaily.Area, (select concat (tblRooms.RoomNo))
FROM ((tblWorkRelease INNER JOIN tblDaily ON tblWorkRelease.WorkReleaseId = tblDaily.WorkReleaseId) INNER JOIN tblTestsPerformed ON tblDaily.QCReportNo = tblTestsPerformed.QCReportNo) INNER JOIN tblRooms ON tblTestsPerformed.TestDescId = tblRooms.TestDescId
WHERE (((tblDaily.QCReportNo)=2947))
ORDER BY tblRooms.RoomNo;
BobRosasAuthor Commented:
I don't think concat works with SQL but I appreciate that you responded.  I will try reposting my question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now