
Microsoft Access Office VB VBA Help and Examples

Recordset Flattening (The function method)

I seem to find that Often SQL just wont do what I want. Typically I often need to list a recordset sideways. For Example if I have a database holding users & their childrens names:

tUser table

User
Barry
Fred
John
Sid

tChildren Table

User	Child
Barry	George
Barry	Jane
Barry	Sue
Fred	Will
John	Dave
John	George
John	Harry
John	Tina
Sid	Sid

If I wanted to out put the following:

User	Children
Barry	George,Jane,Sue
Fred	Will
John	Dave,George,Harry,Tina
Sid	Sid

It would be difficult to do it using a conventional query in Access. The closest I can get is:

User	Child
Barry	George
Barry	Jane
Barry	Sue
Fred	Will
John	Dave
John	George
John	Harry
John	Tina
Sid	Sid

With the query:

SELECT tUsers.User, tChildren.Child
FROM tUsers INNER JOIN tChildren ON tUsers.User = tChildren.User;

Not really what we want as we are getting a line for each child record. I’m afraid we need to create our own User defined function using VBA. It should go something like this:

Public Function ListChildren(ParentName As String) As String
   Dim db As Database
   Dim rs As Recordset
   Dim strSQL As String
   'Open the Recordset
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("Select child from tChildren where User = '" & ParentName & "';")
   ListChildren = FlattenRecordset(rs, , , , ",")
   Set rs = Nothing
   Set db = Nothing
End Function

So now we can get the list we want using the query:

SELECT tUsers.User, listchildren([user]) AS Children
FROM tUsers;

Eagle eyed readers will have noticed a new VBA function in the code – FlatternRecordset. This is a generic function that I have created for just this sort of thing. Here it is:

Public Function FlattenRecordset(rs As Recordset, Optional FieldInitialisor As String = "", Optional FieldTerminator As String = "", Optional RowInitialisor As String = "", Optional RowTerminator As String = "") As String
   Dim fld As Field
   'Deal with empty recordset
   If rs.EOF Then
       FlattenRecordset = ""
        Exit Function
   End If

   'loop through each row of the recordset
    Do
       'Terminate each row
       FlattenRecordset = FlattenRecordset & RowInitialisor
       'If more then one fields cycle through each adding the field seperator
       If rs.Fields.Count > 1 Then
            For Each fld In rs.Fields
               FlattenRecordset = FlattenRecordset & FieldInitialisor & fld.Value & FieldTerminator
            Next
           'strip of the last seporator
            FlattenRecordset = Left(FlattenRecordset, Len(FlattenRecordset) - Len(FieldTerminator))
       Else
            FlattenRecordset = FlattenRecordset & FieldInitialisor & rs.Fields(0).Value & FieldTerminator
       End If
       'Terminate each row
       FlattenRecordset = FlattenRecordset & RowTerminator
       rs.MoveNext
   Loop Until rs.EOF
   'strip of the last seporator
   FlattenRecordset = Left(FlattenRecordset, Len(FlattenRecordset) - Len(FieldTerminator))
   FlattenRecordset = Left(FlattenRecordset, Len(FlattenRecordset) - Len(RowTerminator))
End Function

The function takes a recordset & flattens it inserting the supplied separators either side of each field and record. I find it very useful. At this point I expect someone to mail me and tell me you can do this with a SQL query. I’d love to know how.

A function I use which builds on this is:

Public Function RSasHTML(RecordsetIN As Recordset) As String
RSasHTML = FlattenRecordset(RecordsetIN, "<TD>", "</TD>", "<TR>", "</TR>" & vbCrLf)
End Function

This takes a recordset and converts it into a formatted HTML Table….which is nice! An example of it’s use would be:

Sub HTMLExample()
   Dim db As Database
   Dim rs As Recordset
   Dim strSQL As String
   'Open the Recordset
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("Select * from tchildren;")
   MsgBox RSasHTML(rs)
   Set rs = Nothing
   Set db = Nothing
End Sub

Hope this Improves your life as much as it did mine.

Chris Shepherd

HOME SEARCH SITE PRIVACY POLICY CONTACT

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.