This month, Doug Steele looks at how to handle tables where multiple types of data are in the same table.

I’ll begin by mentioning that this problem came from a daycare that wanted to be able to produce cards that each parent could carry to “prove” that they were entitled to pick up the specific children. It’s not often that you get to help out with a problem that means this much to so many people.

I have a table where each family member is in a separate record (imported from another program that has it that way). Each record has a FamilyId field, as well as a FamilyPosition field (head, spouse, child). I need to make a name tag that gets the name of the family head from one record and the spouse’s name from another record and puts them together on the same line. Then I need to get the names of all of the children together on a second line.

For the purposes of illustration, I’ll assume that the table has the fields listed in Table 1.

Table 1. Details of the Family table.

Since the data is simplified (it only shows a current snapshot of the family, so I don’t have to worry about previous spouses), it’s reasonable to assume that there’s at most a one-to-one relationship between family head and family spouse. That means I should be able to use SQL to relate head to spouse.

One way of doing this is to save a couple of queries: one that returns only family heads, and one that returns only family spouses. The SQL for these queries would look like this:

SELECT ID, FirstName, LastName, FamilyId

FROM Family

WHERE FamilyPosition="Head"

SELECT ID, FirstName, LastName, FamilyId

FROM Family

WHERE FamilyPosition="Spouse"

I’ll name these two queries qryFamilyHead and qryFamilySpouse, respectively, and then write a query that joins the two together:

SELECT Head.FirstName AS HeadFirstName,

Head.LastName AS HeadLastName,

Spouse.FirstName AS SpouseFirstName,

Spouse.LastName AS SpouseLastName,

Head.FamilyId

FROM qryFamilyHead AS Head

LEFT JOIN qryFamilySpouse AS Spouse

ON Head.FamilyId = Spouse.FamilyId;

Running this query against the sample data in the download database gives the results shown in Table 2.

Table 2. Results of running the query on the sample data (given how left joins work, the empty cells are actually Null, not blank).

In Access 2000 and newer, you can actually do this with only a single query:

SELECT Head.FirstName AS HeadFirstName,

Head.LastName AS HeadLastName,

Spouse.FirstName AS SpouseFirstName,

Spouse.LastName AS SpouseLastName,

Head.FamilyId

FROM

(SELECT ID, FirstName, LastName, FamilyId

FROM Family

WHERE FamilyPosition="Head") AS Head

LEFT JOIN

(SELECT ID, FirstName, LastName, FamilyId

FROM Family

WHERE FamilyPosition="Spouse") AS Spouse

ON Head.FamilyId = Spouse.FamilyId;

Regardless of whether you use one query or two, these queries won’t necessarily give the data in the most useful format. Usually, given the data shown in Figure 1, people want to see the names like this:

Jennifer Berry

David & Cheryl Jones

Mark Smith & Mandy Brown

Figure 1

In other words, if there’s no spouse, the desired result should just be “HeadFirstName HeadLastName.” If there is a spouse, the query should check whether HeadLastName and SpouseLastName are the same. If they are, the user will want to see “HeadFirstName & SpouseFirstName HeadLastName.” If not, the desired result is “HeadFirstName HeadLastName & SpouseFirstName SpouseLastName.” This can be handled using a couple of IIf statements in the SQL statement:

IIf(IsNull(Spouse.LastName), _

Head.FirstName & " " & Head.LastName, _

IIf(Spouse.LastName=Head.LastName,

Head.FirstName & " & " & Spouse.FirstName & _

" " & Head.LastName,Head.FirstName & " " & _

Head.LastName & " & " & Spouse.FirstName & _

" " & Spouse.LastName))

Figure 1 shows the results of adding those functions to the query shown earlier.

Okay, that gave me the name of the family head from one record, and the spouse’s name from another record, and puts them together on the same line. How do I concatenate the names of all of the children together as a single line?

Concatenating multiple related records into a single result is a fairly common request with one-to-many relationships, but, unfortunately, it’s not easily supported using SQL, so I’ll look at creating a function to do it. Even though I don’t appear to have a one-to-many situation (since I only have a single table), I recognized that the data could realistically be thought of as comprising two tables–one for the family, and one for the family members–so if we have a concatenation function, it should be of use to us.

What’s required is to return all of the records that are related to one another, and concatenate them into a single field. Working with sets of related records is what Domain Aggregate functions (DAvg, DCount, DLookup, and so on) are all about, but unfortunately there isn’t a built-in DConcatenate function in Access, so I’m going to create one.

The general syntax for Domain Aggregate functions is Dfunction(expr, domain[, criteria]), where Expr is a string expression that identifies the field whose value you want to work with, Domain is a string expression identifying the set of records that constitutes the domain (a table name or a query name), and the optional Criteria is a string expression used to restrict the range of data on which the function is performed.

To this, I’m going to add an additional optional parameter, Separator, which will let me specify what character is supposed to be used to separate the concatenated values. If not supplied, “, ” (a comma followed by a blank field) is used:

Function DConcatenate( _

Expr As String, _

Domain As String, _

Optional Criteria As String = vbNullString, _

Optional Separator As String = ", " _

) As String

Working with a data domain implies that I’m going to need to work with a recordset. I’m also going to need to create a SQL string to create the recordset, as well as a variable to use to hold the concatenated values:

Dim rstCurr As DAO.Recordset

Dim strConcatenate As String

Dim strSQL As String

The SQL string needed to create the recordset relies on the values passed to the function for Expr, Domain, and Criteria:

strSQL = "SELECT " & Expr & " AS TheValue " & _

"FROM " & Domain

If Len(Criteria) > 0 Then

strSQL = strSQL & " WHERE " & Criteria

End If

So the code opens the recordset and then loops through the records concatenating the data into a single variable along with the Separator value.

This code concatenates the values, adding the separator after each value, and then removes the final separator at the end:

Set rstCurr = CurrentDb().OpenRecordset(strSQL)

Do While rstCurr.EOF = False

strConcatenate = strConcatenate & _

rstCurr!TheValue & Separator

rstCurr.MoveNext

Loop

If Len(strConcatenate) > 0 Then

strConcatenate = _

Left$(strConcatenate, _

Len(strConcatenate) - Len(Separator))

End If

Once I’ve looped through all of the rows in the recordset, all’s that left is to clean up:

  rstCurr.Close

Set rstCurr = Nothing

DConcatenate = strConcatenation

End Function

In this situation, the Expr that I’m interested in is FirstName. The Domain, of course, is the table Family. The only records of interest are those where FamilyPosition is “child” and have matching FamilyIds. For instance, if I want the names of all of the children in family 506, the call to DConcatenate would be:

DConcatenate("FirstName","Family", _

"FamilyPosition = 'child' And FamilyId = 506")

This call would return "Jeremy, Julie, Amy."

If you look in the accompanying database, you’ll see that I’ve created query qryFamilyNames, which uses the preceding query and the DConcatenate function to return both the information on the parents and the information about the children:

SELECT Head.FirstName AS HeadFirstName,

Head.LastName AS HeadLastName,

Spouse.FirstName AS SpouseFirstName,

Spouse.LastName AS SpouseLastName,

Head.FamilyId,

IIf(IsNull(Spouse.LastName),Head.FirstName &

" " & Head.LastName,

IIf(Spouse.LastName=Head.LastName,

Head.FirstName & " & " & Spouse.FirstName &

" " & Head.LastName,Head.FirstName & " " &

Head.LastName & " & " & Spouse.FirstName &

" " & Spouse.LastName)) AS DisplayName,

DConcatenate("FirstName", "Family",

"FamilyPosition = 'child' And

FamilyId =" & [Head].[FamilyId]) AS Children

FROM qryFamilyHead AS Head

LEFT JOIN qryFamilySpouse AS Spouse

ON Head.FamilyId = Spouse.FamilyId

Figure 2 shows the results of adding that to the query I showed earlier.

Figure 2

Okay, that’s almost what I wanted. Sometimes the children don’t have the same last name as their parents. Can I get the child’s surname shown as well?

The simple answer is that the DConcatenate function can actually return more than one field. If you change the call to the previous DConcatenate function to this:

DConcatenate("FirstName & ' ' & LastName",

"Family",

"FamilyPosition = 'child' And

FamilyId =" & [Head].[FamilyId])

the query will return the result shown in Table 3.

Table 3. Children with different surnames, result 1.

If what you want, however, is the result in Table 4, it’s going to be a little more work (and it will no longer be possible to use a generic function such as the DConcatenate function from earlier).

Table 4. Children with different surnames, result 2.

What has to be done in this case is open a recordset that returns both FirstName and LastName for the children in a given family. You’ll then need to order the recordset so that rows with the same LastName are grouped together.

For the first row in the recordset, the code concatenates the FirstName to the “working” concatenation string. For each subsequent row, the code determines whether or not the LastName is the same as the previous LastName. If it is, I concatenate a comma and the current FirstName to the working string. If it isn’t, I determine whether the last thing added to the working string was a comma followed by a FirstName, or just a FirstName. If it’s a comma, then I replace it with an ampersand.

In either case, the next step is to add a space and the previous LastName. Once that’s done that, I can concatenate the previous word followed by the new FirstName.

I suspect that the code is less complicated than those instructions. The opening section declares some variables:

Function ConcatChildren( _

FamilyId As Long _

) As String

Dim dbCurr As DAO.Database

Dim rsCurr As DAO.Recordset

Dim intSameLastName As Integer

Dim strChildren As String

Dim strPrevFirstName As String

Dim strPrevLastName As String

Dim strSQL As String

strChildren = vbNullString

I then create the SQL string to return a recordset for all the children in the specified family, ordered by LastName (adding FirstName in the ORDER BY clause isn’t critical to the solution):

strSQL = "SELECT FirstName, LastName " & _

"FROM Family " & _

"WHERE FamilyId = " & FamilyId & _

" AND FamilyPosition = 'child' " & _

"ORDER BY LastName, FirstName"

Set dbCurr = CurrentDb

Set rsCurr = dbCurr.OpenRecordset(strSQL)

Now I look at each record in the recordset that was returned:

With rsCurr

If .RecordCount <> 0 Then

Do While Not .EOF

If strPrevLastName <> !LastName Then

If strPrevLastName doesn’t contain anything, then this is the first record. I use only the first name until I find out the last name of the next child. I used a counter to check whether this is the first name with the given last name:

          If Len(strPrevLastName) = 0 Then

strChildren = strChildren & _

!FirstName

intSameLastName = 1

If strPrevLastName does contain a value, then I know that I’m not on the first record, and that the previous record has a different last name than the current record. I want to add the previous last name to the string that holds my concatenated list. However, I need to check whether or not there’s only one child with the previous last name (in which case I simply concatenate the previous last name), or if there’s more than one (in which case I know that I used a comma when concatenating the previous first name to the list, so I want to change the comma to an ampersand before we continue). I can use the variable intSameLastName to tell me how many children had the same last name:

Else

If intSameLastName = 1 Then

strChildren = strChildren & _

" " & strPrevLastName & _

" and " & !FirstName

Else

strChildren = Left$(strChildren, _

Len(strChildren) - _

Len(strPrevFirstName) - 2)

strChildren = strChildren & _

" & " & strPrevFirstName & _

" " & strPrevLastName & _

" and " & !FirstName

End If

intSameLastName = 1

End If

If the current record has the same last name as the previous record, all I do is concatenate the current FirstName (prefixed with a comma) to my concatenation string. I also have to make sure to increment intSameLastName so that I can have a count of how many children have the same last name:

Else

strChildren = strChildren & _

", " & !FirstName

intSameLastName = intSameLastName + 1

End If

Finally, I save the current names, and move onto the next record:

strPrevFirstName = !FirstName

strPrevLastName = !LastName

.MoveNext

Loop

After the loop is finished, I still have a last name that hasn’t been added to my concatenation string. I use the same logic as before to determine what to add if there’s only one child with the previous last name, or if there are many:

If intSameLastName = 1 Then

strChildren = strChildren & _

" " & strPrevLastName

Else

strChildren = Left$(strChildren, _

Len(strChildren) - _

Len(strPrevFirstName) - 2)

strChildren = strChildren & " & " & _

strPrevFirstName & " " & strPrevLastName

End If

End If

End With

Finally, I clean up and return the working concatenation string:

rsCurr.Close

Set rsCurr = Nothing

Set dbCurr = Nothing

ConcatChildren = strChildren

End Function

Yeah, it’s a lot of work, but it seems to do the trick!

While the original questioner didn’t request this additional functionality, it’s fairly straightforward to extend the model to support allowing additional people to be associated with each family, so that it’s possible to pre-approve a neighbor or relative picking up the children.

You could do this by including a new FamilyPosition value of, say, friend. You would then use a query along the lines of:

SELECT FirstName, LastName,

Null, Null, FamilyId,

FirstName & : " & LastName AS DisplayName,

DConcatenate("FirstName", "Family",

"FamilyPosition = 'child' And

FamilyId =" & [FamilyId]) AS Children

FROM Family

The only reason I included the two Null fields in this query was to ensure that it included the same number of fields as the original query. In this way, it’s possible to UNION together the two queries when trying to produce the report.