Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Infuriatingly simple name and address list

    In these days, this must come up, but I keep having some thing or the other wrong.

    I have tables for household and people; people are marked as children or adults. I want to report hh info (address, phone, etc), and a list of names in the hh.

    But, suppose Jane Dough is married to Richard Roe, and they have two children, jessica Roe and Aaron Roe (they are proper yuppies). Under the D's, I want to see Jane Dough and the list of people, and the same under Richard Roe in the Rs.

    On the other hand, Bill and Mary Conventional also have two children, and they should have only one listing under the Cs.

    This would be easy in a procedural language...

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Infuriatingly simple name and address list

    I think you have several ways of doing this. The way you choose will depend on how you decide to store the data and what questions you want to answer.

    One solution might be this:
    From your post, I assume you have one table for the address details possibly named

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Infuriatingly simple name and address list

    Alternately, you could add an additional field for "Alternate Name" to capture the other family name that the record should be cross-referenced against. Your query could then check to see if surname you are checking for matches either the primary or alternate name. If there is no alternate name needed for the record, the field can be left as Null.

    Sample Data:
    <table border=1><td>LastName</td><td>AltName</td><td>Matches If</td><td>Roe</td><td>Doe</td><td>Either</td><td>Doe</td><td>Roe</td><td>Either</td><td>Conventional</td><td>Null</td><td>Conventional</td>
    </table>

  4. #4
    New Lounger
    Join Date
    Apr 2001
    Location
    Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Infuriatingly simple name and address list

    Thanks to both of you; will think about it and do something. I appreciate your help!

  5. #5
    New Lounger
    Join Date
    Apr 2001
    Location
    Canada
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Infuriatingly simple name and address list

    Got it!
    Create a query which contains only people table's household key, lastname, and a calculated field, (LastName & HHKey) (SELECT DISTINCT).
    Use this as the base of a second query, which contains the fields you want.
    Group the report on (LastName & HHKey) and HHKey, and sort details on People Last and First Names. In the Last-Name HH heading, display the last name from the 1st query. Header for HHid shows household information; details shows people.

Posting Permissions

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