Help with Null Value in Query

[weilerdo]

Hi All, I have searched and tried every post that I could find and can't seem to get this to work. What I have is 2 tables "training_tbl_200" and "topic_tbl_200". I have several querys "qry_training_215" thur "221" that make up my main query "qry_training_222" My main query will not show any records unless there is data in all of my subquerys. I need it to show a 0 if there is no training time in the category. Im not sure that I have explained this correctly so I have attached a copy of the database. I results that I need are if you run the query for August you can see that it should look like. In Feb there was training but nothing is showing up.

Thanks in advance for all your help...You guys and Gals are the best.

Don

 

[grnzbra]

the zipped file doesn't show any joins. If you use inner joins, you will need a matching field on both sides of the join. If you us outer joins, you will get a null when one side isn't there. You can use the NZ() function to change that to a zero.

 

[weilerdo]

Help Please

Thanks grnzbra, but but queries 215 through 221 do have joins in them. I have tried it with all 3 joins and still can not get a 0 value to show. What I get is nothing What I need is this, any help would be greatly appreciated.

Thanks Everyone

 

[grnzbra]

To get a record when you don't have a match on one side of the join, use an Outer Join. Create a regular query and then change change the join properties of your query.

 

[weilerdo]

Thanks grnzba, I hchanged the joins in everyway that I could LEFT, RIGHT INNER and it made no difference. But when I took the criteria fields out of the show it will return a blank field so that is some progress now all I need is to figure out how to get the blank field to be a 0 if no records. I have tried every NZ and IIF function I could find and none of them seem to work with this query.