Query selection (Access 2000)

Topic

New Reply

I have a database of names and addresses and committess that tehse people belong to. It is a Church Database and the selection to find families that are together is by using the surname and streetaddress as the criteria and then they are sorted by a number in order of the family structure. Sometimes one or more members of a family are in the same group. The people in the same group at the same address only need to have one mailing and not a separate mailing to each person. Can any one suggest a way of achieving the mailing labels as required. I have tried several ways but have not been able to achieve the result required.
Just can’t seem to get my head around this one.
Any help would be appreciated.

Reply | Quote

Replies

At our church we have used a family name field and it is used for the first row of the address label.
This allows us to send letters to Mr & Mrs Smith, and also to MIss J Smith at the same address where there are adult children living at home.
Done this way you either leave the spouse family name blank and filter out the blanks or fill it in for all records and then filter for unique fields.

HTH

Peter

Reply | Quote

Many thanks for that Peter and we do the same. The problem comes when There is Mr J Mrs S MrK Miss D Mr L Ms R all memebers of the family and possibly only MsR and Miss D are members of the committe. We do not want to send the item to Mr & Mrs J because that is the main mailing for the family, but only want to send one letter but to the two people or three people from the family.
Regards

Allan

Reply | Quote

Keep the committees small then you can just keep custom lists for them

I think that to list all the applicable names for an address you will need to build a custom function that will loop through your records, filtering against committee membership and appending Titles/Names together and using that as a basis for a new table to print the report from. The big problem with this is that you will soon run out of room on the label when you have

Reply | Quote

Many thnaks Peter, your reply together with the code sent by Hans set me on the trail to doing exactly what I want. Thanks again

Reply | Quote

Perhaps the attaches zipped Access 97 database will help. You’ll have to unzip it and convert it.
Note: it uses DAO. You may have to update the DAO reference in the module.

The database contains a module with a custom function that concatenates names belonging together in the form “A & B & C”. If you want another form, for instance “A, B, C”, change the constant cConcatenator.

The custom function is used in the query qryAddresses.

Reply | Quote

Many Thanks Hans… it works like a charm… just a few alterations and I have exactly wjhat I wanted to achieve… thanks for your help

Reply | Quote