many names one address query

[Bill Thompson]

I have an Access database that I have ran a query on and want to do a mail
merge from that query. My problem is that the database has multiple names
with the same address and I only want to print one name and address label
per address. For example the database has:

Tim Smith 123 W. 99th Clark Iowa 12345
Jane Smith 123 W. 99th Clark Iowa 12345
Freddy Smith 123 W. 99th Clark Iowa 12345

I want to send a letter to:

The Smith Family
123 W. 99th
Clark, Iowa 12345

I need to eliminate the duplicate addresses so that each is unique. Is
there a way to do this in the mail merge part of word or is there a way to
do this in the access query? Please help.

 

[Marin Kostov]

First, build a Query, that when run, asks for Input, such... Oh... Is that
First name and Second name in ON Field?
If YES, you need to find a way to separate them, since as far as I can see,
you need to send mail to everyone with the family name (agent) Smith (the
Matrix - There is No Spoon.
After you have separated the First and Last name, you can make a Query, that
filters the records, based on input, such "Please type Last Name". Then base
the Mail Merge on that Query.

 

[Duane Hookom]

Any solution would depend on what your fields are. Do you have the first and
last names in separate fields? I assume the address, city, state, and zip
are all separate fields.

 

[John Spencer (MVP)]

Perhaps. If you have stored first name and last name is separate fields, you m
i g h t be able to use something like the following UNTESTED query.

SELECT
IIF(Count(LastName)>1, "", First(FirstName))
IIF(Count(LastName)>1, "The " & LastName & " Family", First(LastName)),
LastName, StreetAddress, City, State, Zip
FROM YourTable
GROUP BY LastName, StreetAddress, City, State, Zip

The problem is that you still could end up with duplicates depending on the
names that are entered. If Mr. Smith and Ms. Jones are married and living in
the same household, then you will get two mailings to that address with The
Smith Family and The Jones Family. OR how about, Mr. Smith, Mrs. Smith and
Bobby Jones (from her first marraige) - Again two mailings.

In other words, if it is important enough you may have to restructure your
tables (or at a minimum, add and populate a new field- familyName - to your
current table).

 

[Bill Thompson]

The database has firstname, lastname, address, city/state, and zip.

 

[Duane Hookom]

I would create a totals query that groups by Address, City/State, and Zip
while counting FirstName. Then add this query to your original query and
join the appropriate fields. You can create a calculated column like:
FirstLine: IIF([CountOfFirstName]>1,"The " & [LastName] &"
Family",[FirstName] & " " & [LastName])

 

[Andrew L.]

Bill

I think you just need to right-click in your query design, select properties, and change
unique
values to "yes".

Don't include FIRSTNAME in your query, just LASTNAME, ADDRESS, CITY/STATE, ZIP. You can
even have an expression:
"The "&[LASTNAME]&" family"

HTH
Andrew L.