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. Im 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. Id 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 its 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.