how to "flatten" a list

[asc4john]

I have two tables Items and Options related thru a join table
ItemOptions. When I do a query joining the tables i get a "vertical"
list like.
item1 option1
item1 optoion2
item1 option3
item2 optoion2
item2 option4
item3 optoion2
item3 option4
item3 optoion5

and so on. How do I get some thing like a flatten list.
item1 option1 option2 option3
item2 option2 option4
item3 option2 option4 option5

 

[Guest]

Why would you want to?

 

[BruceM]

You may want to list students in a class, or members of a committee, or
children in a family, or any number of things. One heading and a listing of
everything under that heading is a pretty common need.

To the OP, you could create a report that is grouped by Item. Use the View

Sorting and Grouping in report design view to accomplish this.

Here is a link to a way of doing what you seek:
http://www.mvps.org/access/modules/mdl0004.htm

There have been a number of postings about concatenating subform records. A
Google Groups search for Access concatenate subform records should turn up
some postings. One from Duane Hookom contains the following:

Function Concatenate(strSQL As String, Optional strDelimiter As String = ",
") As String
'strSQL should select a specific group of records
' and only one field
'strDelimiter is the character(s) to use between values
'Exit Function
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strReturn As String
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do Until .EOF
strReturn = strReturn & .Fields(0) & strDelimiter
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
strReturn = Left(strReturn, Len(strReturn) - Len(strDelimiter))
Concatenate = strReturn
End Function

 

[asc4john]

Why would you want to?

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video






- Show quoted text -

i want to tie that query to a form and each field to a text box. the
"vertical" list don't work unless i use vba. so i would like a
"horizontal" list.
Or maybe i'm doing this all wrong.