Vertical rows into horizontal rows comma separated per group (1 Viewer)

[K-CJ]

Hi,

I am creating a street map index and would like to create a function that lists all the index grid blocks per street separated by a comma. The data that I have looks like this:

StreetName___IndexBlock
1st Ave _______F9
1st Ave _______G8
1st Ave _______G9
Bennet St _____D7
Bennet St _____D8
Bloem St ______B3
Woodlands Dr __J7
Woodlands Dr __J8
etc...

I want it to be displayed in this format:

StreetName___IndexBlock
1st Ave ______ F9, G8, G9
Bennet St ____ D7, D8
Bloem St _____ B3
Woodlands ___ Dr J7, J8

I have created a module with the following VBA code (see underneath), but it just loops throught the whole table and comma separates each and every index block entry in the table. I want the function to do it per unique street name (grouped) and preferably create a generic function so that I don't have to specify a strSQL and use it like any other function (sum, average, mid, etc) in the Query layout.

Here's the code that I have so far:
------------------------------------------------------
Public Function fStrIndexBlock()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim strText As String
strSQL = "SELECT KraaifonteinRoads.STREETNAME, KraaifonteinRoads.INDEXBLOCK FROM KraaifonteinRoads;"
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)

Do Until RS.EOF
If strText = Null Then
strText = RS![INDEXBLOCK]
Else
strText = strText & ", " & RS![INDEXBLOCK]
End If
RS.MoveNext
Loop

RS.Close
Set RS = Nothing
Set DB = Nothing
Let fStrIndexBlock = strText
End Function

--------------------------------------------

Can someone please help. I've been searching on the internet a lot and tried various things, but my knowledge of VBA is unfortunately just too limited to figure it out.

Thanks!
Kristin

 

[Uncle Gizmo]

You may find this thread useful:
http://www.access-programmers.co.uk/forums/showthread.php?t=94989

 

[K-CJ]

Thanks Uncle Gizmo, but the code in the module in that zip file example is already what I've got so far. I need to have it listed per unique entry (grouped) and not everthing in the whole table in one row.

 

[Uncle Gizmo]

Whats your Grouping Criteria?

 

[K-CJ]

My grouping criteria is per unique street name. I want to show the index blocks in which each street falls. E.g. 1st Ave falls in index blocks F9, F8, G9 and Bennet St falls in index blocks D7, D8 etc. Do you understand what I mean?

Thanks