Group by with concatenated text field

With a group by clause in a select query I can do something like this
Select Item, ControlNum, RptType, Remarks, sum(Qty)
Group By  Item, ControlNum, RptType, Remarks

What I want to do is group my records but I don't have a Qty to sum but I want to concatenate
the ControlNum. Here is data sample.So the Results would look something like this
Item         ControlNum         RptType              Remarks
IT1            C1                        TestItem            This is a test item
IT1            C2                       TestItem            This is a test item
IT1            C3                       TestItem            This is a test item
IT1            C4                       TestItem            This is a test item
IT2            C8                       TestItem             This is also a Test.

So the Results would look something like this
Item         ControlNum                    RptType              Remarks
IT1            C1, C2, C3, C4              TestItem            This is a test item
IT2            C8                                 TestItem             This is also a Test.

I can't remember how to do this.  Any Ideas? Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
You can use a user defined function with the VB String function Join() to create a comma delimited string as the return value.

First copy the following to a code module (make sure to rename the tblTemp to whatever your table name is)

Function ConcatCtlNmbr(ByVal Item As String) As String
    Dim rsData As New ADODB.Recordset
    Dim strSQL As String
    Dim strItems() As String
    Dim lngRow As Long
    'Open Recordset
    strSQL = "SELECT ControlNum FROM tblTemp WHERE Item='" & Item & "' GROUP BY ControlNum"
    rsData.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    'Move through recordset to populate recordcount
    If Not rsData.EOF Then
        'Set the upper bound of the array
        ReDim strItems(rsData.RecordCount - 1)
        'Loop through each record adding it to the array
        For lngRow = 0 To rsData.RecordCount - 1
            strItems(lngRow) = rsData.Fields("ControlNum").Value
        Next lngRow
    End If
    'Create the comma delimited string with the Join() function
    ConcatCtlNmbr = Join(strItems(), ",")
    Set rsData = Nothing
End Function

Next create a query using the user defined function ConcatCtlNmbr for the ControlNum field and pass the Item field as an agrument to the function such as this:
SELECT tblTemp.Item, ConcatCtlNmbr([Item]) AS CtrlNum, tblTemp.RptType, tblTemp.Remarks
FROM tblTemp
GROUP BY tblTemp.Item, tblTemp.RptType, tblTemp.Remarks;
This should give you the desired results.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
create a sql server function

create function dbo.concatcontrolnum
(@item as char(5)
 ,@rpttype as varchar(10)
 ,@remarks as varchar(100)
Returns Varchar(4000)



Declare @Val varchar(4000)

Select @val=coalesce(@Val+',','')+controlnum
  from YourTable
 Where item=@item
   and rpttype=@rpttype
   and remarks=@remarks
 Order by Controlnum



then execute this sql as a passthru query ,,,

Select Item,dbo.concatcontrolnum(item,rpttype,remarks) as Controlnum
 from (select item,rpttype,remarks
         from yourtable
        group by item,rpttype,remarks
      ) as x
Order by 1,3,4  
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MadIceAuthor Commented:
I believe the other examples would work too but I'm working in Access and this answer was more complete for me. Works great. Only question what if I need to pass two parameters? I thought I need to pass about 5 or 6 but after talking to diffent users, one of the fields RptNo was the only field I need to use as the parameter. Thanks alot and sorry for the delay.

Passing multiple parameters should not be a problem, you just need to modify the ConcatCtlNmbr Function to add however many parameters you wish to pass and then modify the WHERE and possibly the SELECT and GROUP BY clauses in the SQL statement to accomidate those changes.  You'll also need to add the fields you are passing to the function in the Query Design.  Glad I could help, good luck with the rest of your project.

MadIceAuthor Commented:
The problem seems to be trying to pass a memo field. Its cutting off and not working correctly. I was looking into moving this over to Sql Server as a stored procedure and function. Why I didn't want to do that is I'll have to pass a lot of control numbers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.