Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Adelaide,SA, Australia
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query selection (Access 2000)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query selection (Access 2000)

    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

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Adelaide,SA, Australia
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query selection (Access 2000)

    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

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query selection (Access 2000)

    Keep the committees small then you can just keep custom lists for them <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query selection (Access 2000)

    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.
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Adelaide,SA, Australia
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query selection (Access 2000)

    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

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Adelaide,SA, Australia
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query selection (Access 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •