Membership database with individual records

[Laurad]

I have been asked to help with an existing Membership Table with individual records for each Member - including title, first name, surname, addr1, addr2, addr3, addr4 and Postcode.

Many of the individual members are spouses or family members living at the same address, in most cases just 2 per household.

They want to do a mailshot to the members, but in order to save on postage, they would like to be able to create one label for each household showing the names of the individuals and the addres, for example:

Mr. John Smith
Mrs. Anne Jones
23 Cedars Avenue,
Addr2
Addr3
Addr4
Postcode

Because not all people have the same surname, it is important to list each one as above rather than assume Mr. & Mrs. John Smith.

I've wracked my brains trying to figure out a query that will perhaps create one record, but with the two individual names, but cannot figure out how to do it. Any help and advice welcome.
Laura

 

[plog]

You just explained that there could be multiple people per address, but you ultimately want to end up with just 2 names. So explain how you are going to handle addresses with 3 or more people?

What 2 people get selected to be named?

 

[Laurad]

I noticed there was only one instance of a 3rd name, so I am more interested in finding a way of picking out just two and dealing with the 3rd name instance manually for now until I work out how to do one label per address with two individual members.

Thanks
Laura

 

[plog]

Since you didn't answer my question, I will assume I heard: "It doesn't matter".

Here's what I would do:

Create a query based on your table. Bring down the address fields and then create 2 calculated fields:

Addressee1: title & " " & [first name] & " " & surname
Addressee2: title & " " & [first name] & " " & surname

Then make that an aggregate query by clicking the Summation (aka Sigma) sign in the tool by. That will populate a new row in the bottom section underneath every field which says 'Group By'. Underneath Addressee1 change it to 'Max' and underneath Addressee2 change it to 'Min'.

Then use that query as the basis of your report/labels. In the report/labels, for Addressee1, you change the control source to this:

=Iif(Addressee1=Addressee2, "", Addressee1)

 

[Laurad]

Thank you, that's brilliant and the min/max did the trick.
I'm sorry I didn't answer your question, I thought it related to instances where there might be 3 individuals. I don't think it really matters which Member is picked out for the top line and which for the second (if that is what you meant).

Thinking of future possibilities where there might be more instances where there could be 3 or more individuals at the same address, would this be handled in a different way? It's not too important as I think their membership is at it's limit and I would suggest to them that they address cases of 3 or more manually.

Thank you for your help and patience.

 

[plog]

It only needs to be handled in a different way if you have a logical way to determine which 2 should be selected. If you just want 2 names selected without regard for what 2, this will work for however many people share an address.

 

[Laurad]

Thank you again.

Out of interest, and I think it's not important in terms of this particular project as I detected just one instance where there were 3 people at the same address, how could I handle it to pick up 3 or more? Forgive me if I have not understood you correctly. The order in which the individuals is selected is not important.
Thank you again.
Laura

 

[plog]

Off hand, I don't know. Most likely a subquery to pick the top 3 names, then possibly a cross-tab query off of it.

More than 3 or a specific 2 would be a lot harder than just random 2, which is why I pressed you for an answer at the beginning and assumed the one I wanted to hear.

 

[vbaInet]

Would it matter which addressee comes first?

 

[Laurad]

plog, once again I thank you so much for your help and advice and again I am sorry I was not clear in responding to your original question. I know it can be frustrating when all the facts are not known.

This database was populated a long time ago and is not likely to increase too much in its present size, so this will be a one-off query, hopefully, which will see them through for future mailings.

vbInet, no, it doesn't matter which order the labels are printed or who's addressee comes first. We are simply trying to save on postage costs and this will do the trick.

I learn something new every time I post here and am so grateful for everyone's kind patience.
Laura