<%dim crumb crumb="Concatenate Records" Crumb2="Denormalize Fields" crumb1="VBA Concatenate" %> Concatenate Rows
Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Forums  Blockchain Tech  Contact  Privacy  Links

<%if len(crumb2)>2 then response.write crumb2 else response.write ">
<% response.write crumb1 %>

Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions

Visual Basic Function Examples

Concatenate Rows & Records
How to Denormalize Records

Sometimes you'll need to concatenate records into a single text field.  This requirement occurs most often on reports where it is impractical to list many small records down the report. In this example we have patient medical records - in particular we have a table that contains one record for each patient allergy. We have a patient report where we want to list the allergies in one line in a text box - i.e. allergy1; allergy2; allergy3...

In a non-normalized database (spreadsheet) we would layout the spreadsheet with columns, one for each allergy and then there is no need to concatenate the multiple records - we can just concatenate the fixed columns to create a single-line list of allergies.

In a relational database there are overriding reason to normalize the table so that each allergy is listed in a separate record for each patient.  Here is the structure of our M_Patient_alrgy table:

Patient_ID Allergy_ID
4 1
4 2
4 3
4 4

The Allergy_ID is a foreign key to our allergy lookup table:

Allergy_ID Allergy
8 acetaminophen
7 dander
1 aspirin
6 flowers
3 erythromycin
4 macrolides
5 cats
2 Penicillin

You can see our patient has four allergies: Aspirin; Penicillin; Erythromycin; Macrolides.  And that is exactly how we want to show the list of allergies in our report - denormalize the multiple records into a list of allergies separated by a semicolon.  This process is can be called dynamic de-normalizing.

To make querying all the data for the report easier we will load the concatenated string into a little temporary table:

Patient_ID Allergy
4 aspirin; Penicillin; erythromycin; macrolides

The end result is that we will have a field called Allergy that we can place on our report.  Here is the VBA code that concatenates rows into a single text field:

Public Function concat_alrgy(Patient_ID)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim hold_alrgy As String
Set db = CurrentDb
hold_alrgy = ""

'  clear out old list

DoCmd.RunSQL ("delete * from t_patient_alrgy")

'  start creating new list

'  select list of records for this patient

Set rst = db.OpenRecordset("SELECT Allergy " & _
" FROM L_alrgy INNER JOIN M_Patient_alrgy " & _
" ON L_alrgy.Allergy_ID = M_Patient_alrgy.Allergy_ID " & _
" WHERE Patient_ID=" & Patient_ID)

'  skip process if there are no items in the list

If rst.BOF Or rst.EOF = True Then GoTo jump_out

  '  start Concatenate Multiple Records to Text Field

Do While Not rst.EOF
  If hold_alrgy = "" Then
    hold_alrgy = rst!Allergy
    hold_alrgy = hold_alrgy & "; " & rst!Allergy
  End If
  '  end Concatenate Multiple Records to Text Field
Set rst = Nothing

'  load the concatenated  list

DoCmd.RunSQL ("INSERT INTO T_Patient_alrgy ( Patient_ID, Allergy ) " & _
" SELECT " & Patient_ID & ",'" & hold_alrgy & "'")

End Function

In the following example we demonstration gathering multiple records for airline landing and takeoff information to create a single string route.  This example is from our Airline Reservations Software case study.

Do While Not rst.EOF
    ' get the route records
    hold_route = ""
    hold_number_passengers = ""
    hold_connection = ""
    sqltext = "Select airport_abbr,number_passengers FROM L_Airports  INNER JOIN Q_Route_Sorted ON " & _
    "L_Airports.Airport_ID = q_route_Sorted.Airport_ID where reservation_Id=" & rst!Reservation_ID & " order by rsort"
    Set rsroute = db.OpenRecordset(sqltext)
    If rsroute.EOF = True Then
        GoTo skip_route1
    End If
    ' concatenate route and passenger info
    Do While Not rsroute.EOF
        ' concatenate route list
        hold_route = hold_route & rsroute!Airport_Abbr & "/"
        If IsNull(rsroute!Number_Passengers) = False Then
            hold_number_passengers = hold_number_passengers & CStr(rsroute!Number_Passengers) & "/"
        End If

    sqltext = "Select Airline_ID,Flight_No,Connect_Time from M_Passengers where Reservation_ID=" & rst!Reservation_ID
    Set rsroute = db.OpenRecordset(sqltext)
    If rsroute.BOF = True Then GoTo skip_route

    Do While Not rsroute.EOF
        'concatenate connection info
        hold_connection = hold_connection & DLookup("Airline", "L_Airlines", "Airline_id=" & Nz(rsroute!Airline_ID, 0)) & "-" &           Nz(rsroute!Flight_No, " ") & "(" & rsroute!Connect_Time & ") "

    hold_pilots = ""
    hold_aircraft = ""
    'If IsNull(rst!Dispatch_ID) = False Then
        sqltext = "SELECT [M_Reservation-Dispatch].Reservation_ID, Aircraft_Number, Pilot_Initials" & _
        " FROM L_Pilots INNER JOIN (L_Aircraft INNER JOIN (M_Dispatch INNER JOIN [M_Reservation-Dispatch] ON                     M_Dispatch.Dispatch_ID = " & _
    " [M_Reservation-Dispatch].Dispatch_ID) ON L_Aircraft.Aircraft_ID = M_Dispatch.Aircraft_ID) ON L_Pilots.Pilot_ID =                 M_Dispatch.Pilot_ID" & _
    " Where [M_Reservation-Dispatch].Reservation_ID=" & rst!Reservation_ID
    Set rspilots = db.OpenRecordset(sqltext)
    If rspilots.EOF = False Then
        Do While Not rspilots.EOF
            hold_pilots = hold_pilots & rspilots!pilot_initials & "/"
            hold_aircraft = hold_aircraft & rspilots!Aircraft_Number & "/"

    End If
'End If
' writtem
With rst
    If Len(hold_route) > 2 Then
        !Route = Mid(hold_route, 1, Len(hold_route) - 1)
    End If
    If IsNull(hold_number_passengers) = False And Len(hold_number_passengers) > 2 Then
        !PAX = Mid(hold_number_passengers, 1, Len(hold_number_passengers) - 3)
    End If
    If Len(hold_pilots) > 1 Then
        !Pilots = Mid(hold_pilots, 1, Len(hold_pilots) - 1)
    End If
    If Len(hold_aircraft) > 1 Then
        !Aircraft = Mid(hold_aircraft, 1, Len(hold_aircraft) - 1)
    End If
    !Connections = hold_connection
End With


Read more about de-normalized tables in our table design tutorial section.

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>

Microsoft Access 2007, 2010, 2013 & 2016