Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    I'm using Access 2003. I have a fairly simple db.I have a moms table and a kids table. I have them related and everything is working fine. I have a query with the 2 tables where I list each mother and I want a count of the kids. I use the grouping feature and count on the childID field. Works great except. I have a few moms that don't have any children and they do not show up in the list. How do I show them in the list. I tried an IF statement but couldn't get it to work.

    Thanks for any help.
    Deb

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In the Query Design double click the line joining the two tables, and a dialog will popup offering three options. The default is to only return records that match on both sides of the join. Choose the option to show all the mothers.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Once you've done what John suggests then you can use a WHERE clause to show just mother with kids or mothers without kids
    eg for Mothers without kids
    Code:
    SELECT * FROM Mothers WHERE ChildID Is Null

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys. Both worked great. I knew there was a fairly simple way but couldn't remember it. I used to work with Acces quite often but had a son in 2005 and stayed home, so 2 kids later....I have forgotten a lot.

    Thanks,
    Deb

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys. Both worked great. I knew there was a fairly simple way but couldn't remember it. I used to work with Acces quite often but had a son in 2005 and stayed home, so 2 kids later....I have forgotten a lot.

    Thanks,
    Deb

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by masterd View Post
    Hi,
    I'm using Access 2003. I have a fairly simple db.I have a moms table and a kids table. I have them related and everything is working fine. I have a query with the 2 tables where I list each mother and I want a count of the kids. I use the grouping feature and count on the childID field. Works great except. I have a few moms that don't have any children and they do not show up in the list. How do I show them in the list. I tried an IF statement but couldn't get it to work.

    Thanks for any help.
    Deb
    OK, I've got to ask. Isn't having a kid like a major requirement for being a Mom?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by MarkLiquorman View Post
    OK, I've got to ask. Isn't having a kid like a major requirement for being a Mom?
    A truly brilliant non technical question, well done Mark.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not sure why you asked that but YES it is!!! lol
    deb

Posting Permissions

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