Problems with queries and tables

[Guest]

I am new to Access and am working with a preexisting database that I did not
create, so please bear wiith me. I trying to create a query that combines
individual fields to create a mailing list where the names of married couples
appears as Mr. & Mrs. Joe Smith. I'm using the following expression (I think
that is the term) that I copied from an existing query:

fullname: [Title] & " & " & [spouseTitle] & " " & [FirstName] & " " & [mi] &
" " & [LastName] & " " & [Suffix]

The problem is that some of the names have to be manually entered into a
separate table because they don't fit the standard. ie. Rev. Joe and Dr.
Jane Smith. How can I have a query disregard the "fullname" string (above)
if that record is in the table of unusual "fullnames" ? Both tables have a
common field named Household ID.

Thanks in advance for your help.

 

[Guest]

To go one step further, how about the case where they spouse (or partner)
don't have the same last name, or you might not have a spouse/partner at all.
You might have:

Mr. Dave Smith
Ms. Darlene Smith
Mrs. Darlene Smith (widower or divorced, so no spouse)
Mr. & Mrs. Dave Smith
Rev. Joe and Dr. Jane Smith
Rev. Joe Smith & Dr. Jane Jones-Smith

I would recommend that you create a function (fnHouseholdName) that you can
pass values to for all of these fields, and can then use some serious logic
to build the value to get this function.

If you need more help with the function, and can address some of the
alternatives for how you would handle these cases, I'm sure one of us can
help.

Dale

 

[John W. Vinson]

I am new to Access and am working with a preexisting database that I did not
create, so please bear wiith me. I trying to create a query that combines
individual fields to create a mailing list where the names of married couples
appears as Mr. & Mrs. Joe Smith. I'm using the following expression (I think
that is the term) that I copied from an existing query:

fullname: [Title] & " & " & [spouseTitle] & " " & [FirstName] & " " & [mi] &
" " & [LastName] & " " & [Suffix]

The problem is that some of the names have to be manually entered into a
separate table because they don't fit the standard. ie. Rev. Joe and Dr.
Jane Smith. How can I have a query disregard the "fullname" string (above)
if that record is in the table of unusual "fullnames" ? Both tables have a
common field named Household ID.

What I've done in a similar circumstance is to put a field in the Household
table with a preferred mode of address for the family. There are LOTS of
variations - in my case, "Dr. John W. Vinson & Dr. Karen Strickler"; people
with no middle name; etc. etc.


John W. Vinson [MVP]

 

[John Spencer]

DO you have the separate (Unusual table) table related to the mailing table
in the query so that you can see the "UnusualName" and the normal names?

If so, you can use something like the following.

FullName: IIF(UnusualTable.UnusualName is Not Null,
UnusualTable.UnusualName, [Title] & " & " & [spouseTitle] & " " &
[FirstName] & " " & [mi] &
" " & [LastName] & " " & [Suffix] )
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..