Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts

    dumb question: mailing list (2002 SP3)

    I have what must be a simple problem, but I can't see the solution.

    I have a mailing list database. One table holds Individuals: Individual's Number, name, e-mail address, etc. The Households Table holds: Household Number, two places for Individual's Numbers for those Individuals who are members of the household, address (street, city, state, ZIP). Households may have one or two members.

    THe Problem: I want to assemble a list of all the Individuals, each entry in the list having one Individual's Name and the corresponding Household Address. So, where there are two Individuals in a Household, I want two records in the query result, one for each Individual, both of which show the same Household Address. How do i build this Query, please. I hav been attempting this for two days, without success! This has got to be a trivial problem that has been solved a million times!

    Thanks for any Help
    Bob Chapman

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

    Re: dumb question: mailing list (2002 SP3)

    Your table structure is not ideal. Instead of two fields for Individual Numbers in the Households table, you should have none there. Instead, the Individuals table should have a Household Number field. Your problem would be solved.

    You can create a union query to return one or two records per household. You must do this in SQL view. The SQL looks like this, with the appropriate names substituted:

    SELECT [Household Number], [Individual Number 1]
    FROM [Household Table]
    UNION
    SELECT [Household Number], [Individual Number 2]
    FROM [Household Table]
    WHERE [Individual Number 2] Is Not Null

    You can then create a query based on this query and the Individuals table.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: dumb question: mailing list (2002 SP3)

    I knew there must be a solution: thanks for your great help.

    Bob

Posting Permissions

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