making multiple rows into one field

[billybo]

I have tried to find this everywhere, I get close but no cigar! What I'm trying to do is make a query (in Access) that will return condensed information.

Table 1:

id booth
1 430
1 431
1 432
2 540
2 539

What I would like is a query that produces:
id booths
1 430, 431, 432
2 540, 539

Any help would be appreciated. Thanks

 

[sfreeman@co.mer]

As close as I can get...

Does this help at all?

TRANSFORM
tblBooth.Booth

AS
Booth2

SELECT
tblBooth.ID

FROM
tblBooth

GROUP BY
tblBooth.ID

PIVOT
tblBooth.Booth;

 

[Jon K]

To put the booths of an ID into one field, you can use a public function in a Totals Query.

I have attached a database. The public function getBooths(), which is used in the query, is placed in Module1.
.

 

[billybo]

Awesome

It took a little playing with but you guys are great, thanks a bunch!

The code that I finally got to work is below, but it took me a while to figure out that the function kept getting screwed up because I had some null values in my table. All is well now though...


Public Function getBooths(assigned_to) As String
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim sBooths As String

Set cnn = CurrentProject.Connection

SQL = "Select [assigned_to],[id] from [Booth] where [assigned_to]=" & [assigned_to]

rst.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

Do While Not rst.EOF
sBooths = sBooths & ", " & Abs(rst![id])
rst.MoveNext


Loop
getBooths = Mid(sBooths, 3)

Set cnn = Nothing
Set rst = Nothing

End Function