By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,315 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Query distinct and concat

P: n/a
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
Jan 30 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
fo******@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
Jan 30 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.