All in the Family

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.

Field name Data type
Id AutoNumber (PK)
FirstName Text
LastName Text
FamilyId Long Integer
FamilyPosition Text

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:

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

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

HeadFirstName HeadLastName SpouseFirstName SpouseLastName FamilyId
Jennifer Berry 214
David Jones Cheryl Jones 506
Mark Smith Mandy Brown 360

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

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:

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:

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:

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

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:

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

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:

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:

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:

the query will return the result shown in Table 3.

Table 3. Children with different surnames, result 1.

Jason Berry, Chloe Berry
Jeremy Jones, Julie Jones, Amy Jones
Brittany Smith, Jessica Brown

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.

Chloe & Jason Berry
Amy, Jeremy & Julie Jones
Jessica Brown and Brittany Smith

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:

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):


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 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:

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:

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:

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

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:

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.

 Your download file is called Steele_All_Family_AA200511.accdb

About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in Other Topics. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.