Re: [OT] SQL question

["Mark Pollock" <pollocmc@xxxxxxx>]

A bit more involved (and I don't know if performance will be an issue)
but here goes.

You need t  create a function e.g.

Public Function concat(CompanyID As Integer) As String
Dim db As Database
Dim rs As Recordset
Dim sTemp
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT EMPLOYEENAME FROM [TABLE B] WHERE
[COMPANY ID] = " & CompanyID)

While Not rs.EOF
sTemp = sTemp & rs!EMPLOYEENAME & ","
rs.MoveNext
Wend
sTemp = Left(sTemp, Len(sTemp) - 1) ' drops the last comma
concat = sTemp
End Function

Then you create a query like this

SELECT [Table A].[Company Name], concat([Table A].[ID]) AS Expr1
FROM [Table A];

And bob's your uncle...

There may be a neater way of doing this but neatness was never my
strongpoint :)

Mark.
--- In ukha_d@y..., "James Hoye" <james.hoye@s...> wrote:
> > SELECT [Table A].[Company Name], First([Table B].EmployeeName) AS
> > FirstOfEmployeeName
> > FROM [Table A] INNER JOIN [Table B] ON [Table A].ID = [Table
> > B].[Company ID]
> > GROUP BY [Table A].[Company Name];
>
> Taking the example one step further, is it possible t  concatenate
all of
> the 'many' records ont  the one row with a separator?
>
> CompanyName	EmployeeName
> ========================
> Acme Corp	Bloggs, Jones, Smith
> Microshag	Gates, Williams
>
> Can you see what I'm getting at?  We are trying to use hierarchical
> recordsets, and are finding they are quite slow....
>
> James H




____________________________________
Automated Home UK
http://www.automatedhome.co.uk
____________________________________

Your use of Yahoo! Groups is subject t  http://docs.yahoo.com/info/terms/