Merging 2 Records into 1 1

[pwainsworth]

I have a table of peoples personal details. Some are married and I want to write a query that combines the first names of a married couple so that the result of the query only shows one record instead of two. The purpose of this is the result will be used in a mail merge for MS Word.

Does anyone know how this can be done?

 

[RickSpr]

You use what's called a "self-join". Simply add the table twice to a single query grid. The second copy will be named with a "1" suffix on it. Then draw your join lines to show what fields have to match in the two records. You'll probably want to use a left outer join, so that you'll have unmarried people in the result set.

By itself, such a self-join will output each couple twice, once with the partners in one order and once in reverse order. To prevent that, add a selection criterion that relates unequal values with a relational operator. For instance, you might use &quot;(People.FirstName < People1.FirstName) OR (IsNull(People1.FirstName))&quot;. Rick Sprague

 

[pwainsworth]

Thanks for that.

One thing further. In my firstnames field I am combining married people with the format of 'Bill & Sally' but for single people it shows 'Andy & '.

Is there a way of being selective so that just the firstname of one person shows and both firstnames with an ampersand shows for married's?

 

[RickSpr]

Sure! Try this:
FirstName1 & IIf(IsNull(FirstName2), &quot;&quot;, &quot; & &quot; & FirstName2) Rick Sprague