Query distinct and concat

Hi,

I have the following table called "tests" :
id WeekNbr name test hours
------------------------------------------------------------ --------------
1 2007/26 John "testA" 5
2 2007/26 John "testB" 6
3 2007/26 David "testA" 3
4 2007/28 David "testC" 2
5 2007/30 Victor "testD" 1

I want to write a query so that I have as a result one row per person
and per week, as followed

WeekNbr name test hours
------------------------------------------------------------ --------------------
2007/26 John "testA, testB" 11
2007/26 David "testA" 3
2007/28 David "testC" 2
2007/30 Victor "testD" 1

This means that I need to concatenate the values of the test column if
in the same week and same person.
For now, I have only managed to do the job without the test
concatenation the following way:

SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours]
FROM tests
GROUP BY WeekNbr, name

and I get the following:
WeekNbr name hours
-------------------------------------------------------
2007/26 John 11
2007/26 David 3
2007/28 David 2
2007/30 Victor 1


Anyone could help me please?

Thanks so much in advance

Pierrot

Re: Query distinct and concat

foutuguy@gmail.com wrote:
> Hi,
>
> I have the following table called "tests" :
> id WeekNbr name test hours
> ------------------------------------------------------------ --------------
> 1 2007/26 John "testA" 5
> 2 2007/26 John "testB" 6
> 3 2007/26 David "testA" 3
> 4 2007/28 David "testC" 2
> 5 2007/30 Victor "testD" 1
>
> I want to write a query so that I have as a result one row per person
> and per week, as followed
>
> WeekNbr name test hours
> ------------------------------------------------------------ --------------------
> 2007/26 John "testA, testB" 11
> 2007/26 David "testA" 3
> 2007/28 David "testC" 2
> 2007/30 Victor "testD" 1
>
> This means that I need to concatenate the values of the test column if
> in the same week and same person.
> For now, I have only managed to do the job without the test
> concatenation the following way:
>
> SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours]
> FROM tests
> GROUP BY WeekNbr, name
>
> and I get the following:
> WeekNbr name hours
> -------------------------------------------------------
> 2007/26 John 11
> 2007/26 David 3
> 2007/28 David 2
> 2007/30 Victor 1
>
>
> Anyone could help me please?
>
> Thanks so much in advance
>
> Pierrot

I would write a function. In the query builder create a column like
ConcatTestStr : ConcatStuff([ID])

Now create a public function that selects the field test for the id and
loop thru the recordset concatenating field Test.
Public Function ConcatStuff(lngIDID As Long) As String
strSQL = "select ..."
Do while not rst.Eof
ConcatStuff = ConcatStuff & rst!Test
Loop
End Function

Clean Shirt
http://www.youtube.com/watch?v=LbcIF1J_jnI