• Showing data when relationship has Null value

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Showing data when relationship has Null value

    Author
    Topic
    #472174

    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

    Viewing 5 reply threads
    Author
    Replies
    • #1248533

      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.

    • #1248554

      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
    • #1248585

      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

    • #1248586

      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

    • #1248669

      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?

      • #1248676

        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.

    • #1248698

      Not sure why you asked that but YES it is!!! lol
      deb

    Viewing 5 reply threads
    Reply To: Reply #1248669 in Showing data when relationship has Null value

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




    Cancel