asked on

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

Rey Obrero (Capricorn1)

you will need to use a function in a query to do that..

see this article

https://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

ASKER CERTIFIED SOLUTION

AccessMCP

membership

This solution is only available to members.

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

Start Free Trial

Lowfatspread

create a sql server function

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

As

Begin

Declare @Val varchar(4000)

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

Return(@val)

End

then execute this sql as a passthru query ,,,

Select Item,dbo.concatcontrolnum(item,rpttype,remarks) as Controlnum
,rpttype,remarks
from (select item,rpttype,remarks
from yourtable
group by item,rpttype,remarks
) as x
Order by 1,3,4

MadIce

ASKER

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.

AccessMCP

MadIce,

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.

AccessMCP

MadIce

ASKER

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.