Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Question Unanswered: MS Access intricate query

    How to avoid double counting?

    In a table with 3 Columns: "Member", "Spouse", "Number of Children".

    row #1 = Person A, Person B, 3 Children
    row #2 = Person B, Person A, 3 Children
    row #3 = Person C, Person D, 2 Children

    How do I get the total count of Children without double counting?
    How do I get the total count of Person/Spouse without double counting?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums.

    The short answer to your question is "better table design".

    Based on the very limited amount of information you've provided I would suggest you need to have a table of adults, a table of children, a table for spousal relationships, and a table for parent/child relationships and then you can do counts properly.

    Have a look at Database Normalization, it will solve many of your headaches.
    Me.Geek = True

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alainberthiaume
    In a table with 3 Columns: "Member", "Spouse", "Number of Children".
    i was going to say the same thing, redesign your table

    however, this problem is familiar to me, as it is a common homework problem

    (i'm also an SME, with two children from each of two marriages)

    please answer a few questions, and then i can help you with the problem of double counting

    first, which of those columns are declared NOT NULL? what happens when a member and spouse have no children?

    second, are member-spouse combinations unique?

    third, can a member have multiple spouses?

    fourth, is this table really about members, or children? the query seems quite interested in the children
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2009
    Posts
    2
    Thanks to all for your quick reply! Unfortunately the table does not belong to me. Design change is impossible for the moment.

    To answer to nckdryr questions:

    1. which of those columns are declared NOT NULL? Only the Member's column is declared NOT NULL. The member can be single and have a relationship with someone with or without children. He can be single with or without children.

    2. what happens when a member and spouse have no children? The member has a relationship with a person but has no children/dependants.

    3. are member-spouse combinations unique? For the purpose of my question/example, the answer is yes! But the data has relationship instead of spouse which can mean various things. Common law, Same Sex couple etc...

    4. can a member have multiple spouses? Only one spouse! Not legal in Canada to have more!

    5. is this table really about members, or children? Unfortunately I need to get both from the same data set.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. which of those columns are declared NOT NULL? Only the Member's column is declared NOT NULL. The member can be single and have a relationship with someone with or without children. He can be single with or without children.
    please give example rows for
    - single, relationship with someone, no children
    - single, relationship with someone, with children
    - single, no relationship, no children
    - single, no relationship, with children

    2. what happens when a member and spouse have no children? The member has a relationship with a person but has no children/dependants.
    okay, this is covered in the examples for 1.

    3. are member-spouse combinations unique? For the purpose of my question/example, the answer is yes! But the data has relationship instead of spouse which can mean various things. Common law, Same Sex couple etc...
    okay, "spouse" is a general term, but my question was oriented more towards finding a particular member -- if mary and fred are both in the sample, are the stored as member=mary and spouse=fred, or the other way around? your example data shows the same 2 people in rows 1 and 2, so the member-spouse combinations are not unique

    4. can a member have multiple spouses? Only one spouse! Not legal in Canada to have more!
    sorry, this is wrong -- i myself have been married twice, and each marriage produced 2 children
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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