query that merges records from a sub-table

[Guest]

I have the following tables and fields:

tbl: Household
fields:
1 - Surname
2 - StreetAddress
3 - Suburb
4 - Phone (etc)

tbl: Person (many-to-1 with Household)
1 - Title
2 - FirstName
3 - MaritalStatus (single, married, divorced etc)
4 - HouseholdStatus (husband, wife, single adult, child)
5… etc

I need to send a mail merge letter more or less as follows:

<Title> <FirstName> <Surname>,
(address fields)

Dear <FirstName>

I can write a query and a mail merge letter that works for single records in
Person, but I can’t work out how to structure a query to merge the records
for a husband and wife. ie, how do I produce “Mr and Mrsâ€, and “Dear John
and Jane�

Many thanks.

 

[Allen Browne]

Peter, I seriously doubt that it is possible come up with a satisfactory
automated process that programmatically combines the names to produce the
addressee name and saluation.

There are so many possible combinations of names. Even some husbands and
wives have different surnames, and then you start adding the various
surnames from the Brady-bunch-type family. Some people do not like you using
a title in their name, and others want you to use their title and not their
Christian name. Then you add the Asian households where the family-name
preceeds the personal name. Clearly this stuff requires manual entry.

It seems to me that the only satisfactory solution is to actually provide
fields to store the addressee and salutation. If you wish, you can try to
automate the process by using the AfterUpdate event of the various text
boxes where the name is entered to try to generate an automated name, but
even that might be counterproductive in some databases.

So, consider adding fields to store the Salutation and Addressee in the
tables.

BTW, if you need to do some mailings to the individuals and some to the
households, take a look at:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html
The article includes a sample database the illustrates a flexible way of
typing people together in households, companies, groups, and informal
combinations of various types.

 

[John Vinson]

I need to send a mail merge letter more or less as follows:

<Title> <FirstName> <Surname>,
(address fields)

I agree with Allen. I've got a very similar issue in the
church-membership database I wrote for my church; Allen's solution of
having a "preferred form of address" in the Family table is what I
eventually used, after a couple of polite objections from parishoners
and several letters discarded and reprinted manually because the
salutation was simply too dreadful to condider... <g>


John W. Vinson[MVP]

 

[Guest]

Allen,

Not for the first time I get more than I bargained for in this discussion
group!

I’ve read your article and downloaded your sample database. Most of what
you’ve said makes pretty good sense, but just to clarify one thing. If I
follow my table layout, I think you’re saying that I really need 2 Salutation
fields - one in tbl.Household, for letters sent to the family or the couple;
and one in tbl.Person, for letters sent to the individual. Have I read you
correctly?

 

[Allen Browne]

Yes, that is correct.

Unless you follow the example database you downloaded, where individuals and
households are all in the one table--tblClient--so there is only one place
where you have to get the records from to send a mailing that could include
individuals and also households. In the example database,
tblClient.Salutation is whatever should follow "Dear" in a merge letter, and
tblClient.Addressee is the name as it should appear in the envelope.