Showing data when relationship has Null value

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote