I have a membership database. Spouses are in a related file for Joint membership. They sometimes have different lastnames. I produce a Roster of members. How can I have an alphabetical list of members to include spouses with a different Lastname? Lastname and spouse::lastname (Only if <>lastname) as one sort
In the Member table, create a calculation field with a text result:
lastname & If ( lastname ≠ spouse::lastname; " " & spouse::lastname )
Sort by this field. It may be slow if you have a lot of members (say 50,000) because it is necessarily unindexed as the result cannot be stored.
So this will produce a list like:
Is that what you want?