Expression for mailing list

[SoClueless]

Hi all!

As usual, advice needed at the 11th hour...usually I produce mailing label reports for a newsletter where there are multiple individuals at some street addresses. I have it set up to just print the last name and the address so that only one gets to each household, using "show totals" of course.

A new twist, our director would like to include the first name of each person in the household on the label, still sending one to each household. ie "John and Mary Smith" instead of just "Smith" although each individual is a seperate record. Can this be done?

Any help would be appreciated. Thanks.

 

[grnzbra]

I haven't tried anything like this, so I don't know if it will work. It's something to think about as a starting point. You will have to play with it

You could create a temporary table and fill it from a query that has only households that have more than one record. (you could actually just use a query, but I am explaining it this way so that I don't keep referring to queries later on.)

Now create a query based on two occurrances of this table joined on all elements of the name and address EXCEPT the first name. Include all fields except the first name. For that, create a calculated field:

CompFirstName: TempTable.FirstName & " and " & TempTable1.FirstName.

This will still give you more than one record per houslhold, but if you specifiy the Unique properties, you should get only one.
You'll have to play with it if you have 3 or members in each household.

Now that you have your multiple member households taken care of, create a union query with this query and a query that gives you only the households that had one member.

Another thought would be to create the temporary table and add a field called HouseholdMemberNumber. I think you would have to fill this by looping through a sorted set of the records and doing a controled break on the household and setting the HouseholdMemberNumber by increments of 1 and then creating a calculated first name field based on the household member number's first name. Perhaps someone more expert with SQL than I am could come up with a SQL statment that would create the incrementing HouseholdMemberNumber.

Good luck.

 

[SoClueless]

Thank you, I've printed your answer out and will try to see if I can figure it out. Really appreciate it!!

 

[grnzbra]

In third paragraph:
Assuming there's a key, join on the key and forget the other joins.

 

[grnzbra]

No. Forget my last comment. Each key is different and you are looking for 2 or more records that are the same. Play with joins on the last name and address elements.

 

[colinb]

Here's a slightly simpler (if more limiting) possibility:

In your query use this in place of forename

fnms:iif(max(fnm)=min(fnm),max(fnm), max(fnm) & " and " & min(fnm))

The only trouble with this is you can only have a maximum of two names per household. Enough in most cases I would say.