Re: how to "flatten" a list

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A55B4EE8-EB28-445D-AEFA-0D1C03EBA837@xxxxxxxxxxxxxxxx

Why would you want to?

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


"asc4john" wrote:

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




.

Relevant Pages

  • Why is my code not working the way I intend it to?
    ... Dim FirstCell As Range, LastCell As Range ... Dim Wordstring As String ... Dim NANDMVARSTRING As String ... Case "siRNA name" 'Heading That Goes to B ...
    (microsoft.public.excel.programming)
  • Re: NOT WORKING..Getting XLA files to work with Excel
    ... Dim FirstCell As Range, LastCell As Range ... Dim Wordstring As String ... If Tried2 Then Exit Sub ... Case "siRNA name" 'Heading That Goes to B ...
    (microsoft.public.excel.programming)
  • Re: Extracting Data from Composite Field
    ... Function libUnravel(strDesc As String, ByVal n As Integer) As Variant ... Dim varElements As Variant ... Dim strReturn As String ... If lngPos> 0 And lngPos < Len) Then ...
    (comp.databases.ms-access)
  • Re: dao.property fld.Properties Values
    ... Dim tdf As DAO.TableDef ... Dim fld As DAO.Field ... Function GetDescripAs String ... Dim strReturn As String 'Name to return ...
    (microsoft.public.access.modulesdaovba)
  • Re: dao.property fld.Properties Values
    ... Dim tdf As DAO.TableDef ... Dim fld As DAO.Field ... Function GetDescripAs String ... Dim strReturn As String 'Name to return ...
    (microsoft.public.access.modulesdaovba)