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)
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)
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.
… …
