Alphabetizing Spouse's Last Name when Different

[BuckyBall]

Hello All,

Thanks in advance. My database lists couples information. Field info includes first name, midddle name,last name, spouse first name, spouse middle name, spouse last name. I publish an address book through a report. Currently, it is listed alphabetically by last name. I'd like to include spouse last name(when different than last name)as part of the alphabitization followed by the information to see under (or go to) lastname, so I am not duplicating the information about a couple. There are many spouses in my database with different last names and this would make things easier for people to look up a a name by someones last name. Here is the current expression which alphabetizes and creates an address label (As you can guess, the fieldnames came from an old DOS program but I think you get the gist of the meanings.)

addresslabel: IIf([slstname]=[lastname] Or [slstname] Is Null,IIf([sfstname] Is Null,[frstname] & " " & [midname] & " " & [lastname],[frstname] & " & " & [sfstname] & " " & [smidname] & "" & [lastname]),[frstname] & " " & [lastname] & " & " & [sfstname] & " " & [smidname] & "" & [slstname])

Thanks again,

Bucky

 

[BuckyBall]

Here is some more helpful info. I run 98SE and am using Access 2000.

Bucky

 

[FancyPrairie]

I'm not sure I would bother with the message see under (goto). Kind of annoying when trying to look someone up. However, you could add another field to your table that somehow identifies who the spouse is. For example, the field could contain the two names concactenated together. (i.e. Susan Smith and Tom Jones would be SmithJones). Now you know who belongs to whom. This could also solve other problems.