combining fields in query

[Guest]

HELP!

THE SITUATION:

NAME table contains 1 record for each unique first_name and last_name.

ADDR table contains addresses for people in the NAME table.

There is a one to many relationship between the ADDR and NAME tables.

Some ADDR records are used by multiple NAME records (i.e., multiple people
live at the same address-e.g., married).

Some ADDR records have only one NAME record.

Example:

Jane Doe-->addr1
John Doe-->addr1
Abraham Lincoln-->addr2

THE QUESTION:

How do code a query so that I get the following result (to be used for
addressing envelopes)

Jane & Joe Doe Addr1
Abraham Lincoln Addr2

or

First_name (First_name) Last_name Address [where (First_name) is only
present when Address is a duplicate]

Appreciate your suggestions.

thanks.

 

[Allen Browne]

Hmm. If is possible to write a VBA function that combines the names for an
address and returns a string to use in the addressee panel. Here's an
example:
http://www.mvps.org/access/modules/mdl0004.htm
It doesn't combine the names in the way you asked, so you will need to
modify it to suit your needs.

In practice, there are many issues that make that approach undesirable. How
do you determine the order of names on the panel? (You probably don't want
the 1-year-old listed first.) Could there ever be times when 2 people at one
address should receive separte mailings (e.g. students at a boarding house)?
Could there be people who should not be included in the mailing (opt out)?
How do you handle people with different surnames? For example, if a Mrs Jane
Doe has a son (Paul Doe) when she marries Phil Jones, and she keeps her
exising name, are they happy to get a label that reads:
Mr & Mrs Paul Doe, and Phil Jones
The point is that there is no way to define the logic here for every kind of
human relationship.

Therefore a better solution might be to record households (rather than
addresses), and to set up your mailing lists where the client can be a
household or an individual, as needed. If that sounds useful, there is a
downloadable example here:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

Hopefully NAME is just an example, and you don't really have a table with
that name. If you do, it may cause you grief, since almost everything in
Access has a Name property, so Access is likely to get it confused with the
Name of the form, or the Name of the report, or the Name of ... For an
extensive list of the names that can cause you problems, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HELP!

THE SITUATION:

NAME table contains 1 record for each unique first_name and last_name.

ADDR table contains addresses for people in the NAME table.

There is a one to many relationship between the ADDR and NAME tables.

Some ADDR records are used by multiple NAME records (i.e., multiple people
live at the same address-e.g., married).

Some ADDR records have only one NAME record.

Example:

Jane Doe-->addr1
John Doe-->addr1
Abraham Lincoln-->addr2

THE QUESTION:

How do code a query so that I get the following result (to be used for
addressing envelopes)

Jane & Joe Doe Addr1
Abraham Lincoln Addr2

or

First_name (First_name) Last_name Address [where (First_name) is only
present when Address is a duplicate]

Appreciate your suggestions.

thanks.

 

[Guest]

Thanks so much Allen.

I will work through this info and see if I can get it to work for this
situation.

I work at Hearst Castle in California (www.hearstcastle.com) and we give
evening "Living History" tours that are populated by DOCENTS who dress in
1930s clothing and help make the place "come alive" for our tour groups.

Some of the docents live at the same address and some live "alone." Most
who live together have the same last name but a few do not. A couple docents
also have a child living with them.

The previous two tables in the database (I think simply built by importing
Excel spreadsheets) had FirstName and LastName in BOTH tables (!) along with
SecondFirstName---so when they ran the MailMergeQuery "John & Jane Doe 111
Oak Street Los Angeles...." showed up in one query record. btw "& Jane" was
the value in SecondFirstName!!!

If two docents living together had different last names, the fields in the
OLD database tables looked like this:

Last Name FirstName SecondFirstName
Smith John Doe & Jane

Mail Merge produced:

John Doe & Jane Smith.... (YIKES!!)

I came along and redesigned the tables, merged/split the data (lots of
wierdities besides "& Jane") BUT then the new MailMergeQuery gave me this:

John Doe 111 Oak Street Los Angeles...
Jane Doe 111 Oak Street Los Angeles...
-or-
John Doe 111 Oak Street Los Angeles...
Jane Smith 111 Oak Street Los Angeles...

We can't afford, and do not want, to stuff/send two envelopes to the same
address.

More than you might want to know but, as I said first off, I will work
through the info you provided and see what can be done.

I like the database example. You do good work. Thanks!
--
Joanne

Hmm. If is possible to write a VBA function that combines the names for an
address and returns a string to use in the addressee panel. Here's an
example:
http://www.mvps.org/access/modules/mdl0004.htm
It doesn't combine the names in the way you asked, so you will need to
modify it to suit your needs.

In practice, there are many issues that make that approach undesirable. How
do you determine the order of names on the panel? (You probably don't want
the 1-year-old listed first.) Could there ever be times when 2 people at one
address should receive separte mailings (e.g. students at a boarding house)?
Could there be people who should not be included in the mailing (opt out)?
How do you handle people with different surnames? For example, if a Mrs Jane
Doe has a son (Paul Doe) when she marries Phil Jones, and she keeps her
exising name, are they happy to get a label that reads:
Mr & Mrs Paul Doe, and Phil Jones
The point is that there is no way to define the logic here for every kind of
human relationship.

Therefore a better solution might be to record households (rather than
addresses), and to set up your mailing lists where the client can be a
household or an individual, as needed. If that sounds useful, there is a
downloadable example here:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

Hopefully NAME is just an example, and you don't really have a table with
that name. If you do, it may cause you grief, since almost everything in
Access has a Name property, so Access is likely to get it confused with the
Name of the form, or the Name of the report, or the Name of ... For an
extensive list of the names that can cause you problems, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HELP!

THE SITUATION:

NAME table contains 1 record for each unique first_name and last_name.

ADDR table contains addresses for people in the NAME table.

There is a one to many relationship between the ADDR and NAME tables.

Some ADDR records are used by multiple NAME records (i.e., multiple people
live at the same address-e.g., married).

Some ADDR records have only one NAME record.

Example:

Jane Doe-->addr1
John Doe-->addr1
Abraham Lincoln-->addr2

THE QUESTION:

How do code a query so that I get the following result (to be used for
addressing envelopes)

Jane & Joe Doe Addr1
Abraham Lincoln Addr2

or

First_name (First_name) Last_name Address [where (First_name) is only
present when Address is a duplicate]

Appreciate your suggestions.

thanks.