asked on

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?

ASKER CERTIFIED SOLUTION

dready

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BobRosas

ASKER

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;
Thanks!

BobRosas

ASKER

I don't think concat works with SQL but I appreciate that you responded. I will try reposting my question.