Combining Multiple Records into one field

[MFlood7356]

I've got a query that I am using to generate a report but currently it is a little bit too long for my liking. I was wondering if it is possible to combine multiple records within the same field? My query currently looks like this (Disregard Center. I am no longer using it for anything): Center|Team Description|1|2|3|4|Pool|Shift What I want to be able to do is combine names into one field. EX: suppose I have two people Smith, John and Doe, John. Both John and Joe have the same Team Description and both are on Team 1 (what 1 stands for). Currently they display in two separate fields. What I would like to do is have them display in the same field to save pages in my report. Here is the current code for my query:

SELECT [ero members].Center, [ero members].[Team Description], IIf([ERO Members].Team="1",[ERO Members].Person,Null) AS 1, IIf([ERO Members].Team="2",[ERO Members].Person,Null) AS 2, IIf([ERO Members].Team="3",[ERO Members].Person,Null) AS 3, IIf([ERO Members].Team="4",[ERO Members].Person,Null) AS 4, IIf([ERO Members].Team="Pool",[ERO Members].Person,Null) AS Pool, IIf([ERO Members].Team="Shift",[ERO Members].Person,Null) AS Shift FROM [ero members];

Here’s what I’ve found online and manipulated a little:

   Public Function fConcatEMailAddr()       Dim MyDB As DAO.Database       Dim rstEAddr As DAO.Recordset       Dim strBuild As String          Set MyDB = CurrentDb       Set rstEAddr = MyDB.OpenRecordset("ERO_Members", dbOpenForwardOnly)          With rstEAddr         Do While Not .EOF           If ![1]  "" Then             strBuild = strBuild & ![1] & "||"           End If             .MoveNext         Loop      With rstEAddr         Do While Not .EOF           If ![1]  "" Then             strBuild = strBuild & ![2] & "||"           End If             .MoveNext         Loop      With rstEAddr         Do While Not .EOF           If ![1]  "" Then             strBuild = strBuild & ![3] & "||"           End If             .MoveNext         Loop      With rstEAddr         Do While Not .EOF           If ![1]  "" Then             strBuild = strBuild & ![4] & "||"           End If             .MoveNext         Loop      With rstEAddr         Do While Not .EOF           If ![1]  "" Then             strBuild = strBuild & ![Pool] & "||"           End If             .MoveNext         Loop      With rstEAddr         Do While Not .EOF           If ![1]  "" Then             strBuild = strBuild & ![Shift] & "||"           End If             .MoveNext         Loop       End With          rstEAddr.Close       Set rstEAddr = Nothing          fConcatEMailAddr = Left$(strBuild, Len(strBuild) - 1)       End Function

This would work I’m just unsure how to incorporate the Team Descriptions so all of the Team 1,2,3,4.. etc values don’t get grouped together in one big field.

[jzwp11]

Out of curiosity, do you only have the 1 table? If so, then your database is not structured properly.

It looks like you have centers and each center has many teams (one-to-many relationship) and each team has many members (another one-to-many relationship). Can a member of one team also be a member of a second team?

[MFlood7356]

Yes I have the one table ERO Members and the given Query. I no longer have any need for center so I have it hidden. Should I delete it totally? No persons can be a member of two teams.

[jzwp11]

I don't know much about your application, so you will have to make the call on whether you can delete Centers. I was more concerned about your table structure. Since a team can have many members, your tables should look like this:

tblTeams
-pkTeamID primary key, autonumber
-txtTeamName


tblTeamMembers
-pkTeamMemberID primary key, autonumber
-fkTeamID foreign key to tblTeams the foreign key field must be a long integer number datatype to match the autonumber datatype
-txtFName
-txtLName

The prefixes are as follows:
pk denotes primary key
fk denotes foreign key
txt denotes a text field

With this structure you would use a query based on table teams and use a custom function similar to what Allen Browne shows here, to concatenate the names of the team members. A word of caution though, the custom function will only allow 255 characters (like any field with the exception of a memo field), so if you have a large number of team members or very long names, some data will get truncated