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

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
1 Solution
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.

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  
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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