Display Field Data Horizontally

Transform, Concatenate Examples

A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done.

Display Field Data Horizontally

Display Field Data Horizontally

Video 1  (4:53)

Good database design requires you to store your data Vertically. However it’s difficult to display this Vertical, columnar data in forms and reports. Also, in the case of an email list, you may need it convert it from the “Vertical” to “Horizontal” for use in VBA code to send multiple emails.

In this video I demonstrate how to extract this columnar data and concatenate it into a string suitable for these type of uses…There is enough information in the video for you to be able to reconstruct the demonstration yourself.

If you want to save yourself a bit of time, then by all means you can download the sample database for a small fee..

Video 1  (4:53)

Nifty Access YouTube Thumb Nail
Play Video

VBA Code - Concatenate Email Addresses - Sample - From Video 1

				
					Public Function fConcatList()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
    
Dim strSQL As String
Dim strText As String

strSQL = "SELECT tblContact.MailToHere FROM tblContact" 'This is just a query like you would build
                                                        'in the query builder, but it is in text format
Set DB = CurrentDb

    'Open a Recordset and loop through it to fill the text box txtTest
    
    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!MailToHere
                Else
                strText = strText & ", " & RS!MailToHere
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

    fConcatList = strText

End Function      'fConcatList

				
			

… …

Complex Data Horizontally

Complex Data Horizontally

Video 2  (6:32)

In the above example “Display Field Data Horizontally” I demonstrate how to compile a list of email addresses which are displayed in a text box. The results could just as easily be passed to another function designed to email the list of clients stored in a table. It would also be possible to provide a query (as an SQL string) with a where clause, making it possible for you to email a subset of clients based on some sort of criteria.

It is always desirable to have the SQL Statement build itself as it were. In this Video “Complex Data Horizontally” I demonstrate how to build the SQL Statement with a WHERE clause that limits the records extracted using the batch number and serial number of the item. The item is a very sophisticated and expensive device somewhat similar to a mobile phone. You just could not manually create the necessary queries effectively.

In this YouTube I’m going to show you one way to go about this.

Video 2  (6:32)

Nifty Access YouTube Thumb Nail
Play Video

VBA Code - Concatenate Repair Description - Sample - From Video 2

				
					Option Compare Database
Option Explicit

Const strSQLWhere As String = "SELECT tblUnitRepair.UnitRepairBatchNo, " & _
"tblUnitRepair.UnitRepairSerialNo, tlkpRepair.RepairDesc " & _
"FROM tlkpRepair INNER JOIN (tblUnitRepair INNER JOIN tblRepairLog " & _
"ON (tblUnitRepair.UnitRepairSerialNo = tblRepairLog.RepLogSerialNo) " & _
"AND (tblUnitRepair.UnitRepairBatchNo = tblRepairLog.RepLogBatchNo)) " & _
"ON tlkpRepair.RepairID = tblRepairLog.RepLogRepair " & _
"WHERE (((tblUnitRepair.UnitRepairBatchNo)="

Const strSQLAnd As String = "AND ((tblUnitRepair.UnitRepairSerialNo)="
Const strSQLOrder As String = "ORDER BY tlkpRepair.RepairDesc"
Const strSQLParenRHS1 As String = ") "
Const strSQLParenRHS2 As String = ")) "

Public Function fConList(intBatchNo As Integer, strSerialNo As String)
Dim DB As DAO.Database
Dim RS As DAO.Recordset
    
Dim strSQL As String
Dim strText As String

strSQL = strSQLWhere & intBatchNo & strSQLParenRHS1 & _
strSQLAnd & "'" & strSerialNo & "'" & strSQLParenRHS2 & strSQLOrder
Set DB = CurrentDb

    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
    
    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!RepairDesc
                Else
                strText = strText & ", " & RS!RepairDesc
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

Let fConList = strText
End Function
				
			

… …

Posts Related to this Blog:-

The link(s) below relate to various discussion(s) related to this Blog.

Visit these and you may find someone else has already solved the problem you are having. You could also pose a new question to clarify the process of using this technique for concatenating vertical lists into a horizontal display of information.

… …

More Useful Stuff HERE:-

Leave a comment

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.