Query / SQL to Join Table Entries on Conditions?

[mfleming]

Hello.

I'm trying to create a report so I can print envelopes for all the guest at our wedding.

I have a table called "Addresses" that has everything in it.
The problem I'm having is displaying the names when creating a report to do mailing envelopes.

Info in the Addresses Table:
FirstName = blah blah
LastName = blah blah
SpouseName = blah blah

I have three different scenarios:

FirstName LastName ie. Joe Blow
FirstName & SpouseName LastName ie.Joe & Kelly Blow
FirstName LastName & SpouseName ie. Joe Blow & Kelly Son

Question:

How to I create the report to display the proper mailing address for each entry with having all three different scenarios?

I'm able to join the table columns fine, but I don't know how to condition them to print only what should be printed for proper mailing address.

Any suggestions would be great.

Thanks

 

[mfleming]

I have created a Query that has:

- MailingName1: [Addresses].[FirstName] & ' ' & [Addresses].[LastName]

- MailingName2: [Addresses].[FirstName] & ' & ' & [Addresses].[SpouseName] & ' ' & [Addresses].[LastName]

- MailingName3: [Addresses].[FirstName] & ' ' & [Addresses].[LastName] & ' & ' & [Addresses].[SpouseName]

Not sure if that helps at all or if I'm way off.

 

[mfleming]

Any suggestions? I know this shouldn't be that hard but I'm just a newbie with this.

 

[randy700]

How about....

Add another field to the Address table. Call it something like MailCode with type number. In this field, use the numbers 1, 2, 3, etc to correspond with your MailingName scenarios.

In the query used to create your report, include a field such as:
MailingName: iif(MailCode = 1, FirstName & " " & LastName, iif(MailCode = 2, FirstName & "&" & SpouseName & " " & LastName, FirstName & " " & LastName & "&" & SpouseName))


Randy