Concatenate Multiple Records and Rows

Concatenate Multiple Records and Rows
How to Denormalize Records

Sometimes you’ll need to concatenate multiple 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.

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 CreateQueryDef Find Database Path Detail-Master Update
Data Field Validation Field Value New-Old FindFirst Access Version
Global Variable Parameter Global Variables Active Labels Files List Box
Mail Merge Quick Sort Select Case Access Transactions