Handling Null Values in Access Query

[BurtonBach]

I previously wrote the following query which gives me that data I wanted and currently returns 436 records:

SELECT qryTonnageSelect.Jobs_Job_ID, qryTonnageSelect.SumOfTransactions_TonsNetWeight, qryProfitability.Margin, jcjob.name, qryProfitability.SumOfcost, qryProfitability.SumOfSumOfcost, qryProfitability.DollarMargin
FROM (qryTonnageSelect INNER JOIN qryProfitability ON qryTonnageSelect.Jobs_Job_ID = qryProfitability.jobnum) INNER JOIN jcjob ON qryProfitability.jobnum = jcjob.jobnum
ORDER BY qryTonnageSelect.Jobs_Job_ID;

I would now like to add another field (man hours) that comes from a subquery. I'm running into a problem in that if the subquery does not have a value of man hours (SumOfSumOfhour s) for for the above query for a particular record then the whole record is not returned and it cuts my data down to only 202 records.

I would prefer it keep the record and either leave the field null or replace it with a 1. Is there a way to do this. The following is the state of the query when I became stumped. Please help. Thank you.

SELECT qryTonnageSelect.Jobs_Job_ID, qryTonnageSelect.SumOfTransactions_TonsNetWeight, qryProfitability.Margin, jcjob.name, qryProfitability.SumOfcost, qryProfitability.SumOfSumOfcost, qryProfitability.DollarMargin, qryPaverHourGroupforTonsRep.SumOfSumOfhours
FROM ((qryTonnageSelect INNER JOIN qryProfitability ON qryTonnageSelect.Jobs_Job_ID = qryProfitability.jobnum) INNER JOIN jcjob ON qryProfitability.jobnum = jcjob.jobnum) INNER JOIN qryPaverHourGroupforTonsRep ON qryTonnageSelect.Jobs_Job_ID = qryPaverHourGroupforTonsRep.jobnum
ORDER BY qryTonnageSelect.Jobs_Job_ID;

[puppydogbuddy]

You can wrap your manhours with the nz (null to zero) function as shown below. If you don't want the nulls to convert to zero, you can replace the null with a 1 instead.

nz(qryPaverHourGroupforTonsRep.SumOfSumOfhours, 0)  <<converts nulls to 0

nz(qryPaverHourGroupforTonsRep.SumOfSumOfhours, 1)  <<converts nulls to 1

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

[NeoPa]

I think in this case (Nz() is very useful for working with Nulls) that you need to look at your joins. A full INNER JOIN to the subquery will select only those records which have matching values in the fields linked on. IE. They exist on BOTH sides of the JOIN.
You should consider using a LEFT JOIN instead.

PS. I would have tried to look at the SQL but it doesn't even seem to have any Man Hours field in it so I'd just be guessing what you are doing.