Trouble with a Select Statement

[mrfixit1170]

Hello all,
I am having some trouble figuring this little issue out. In this Select statement I am trying to total numbers from 2 queries, I have used the IIf(IsNull(),0() to capture if their is a null record, but what happens if because of the join there is no record?

 
SELECT qryReportAVUMSD.EI_ID, qryReportAVUMSD.MOS_ID, IIf(IsNull([qryReportAVUMSD].[SDMH]),0,([qryReportAVUMSD].


[SDMH])+IIf(IsNull([qryReportAVUMSE].[SDMH]),0,([qryReportAVUMSE].[SDMH]))) AS TMH


FROM qryReportAVUMSD 


INNER JOIN qryReportAVUMSE ON (qryReportAVUMSD.EI_ID = qryReportAVUMSE.EI_ID) AND (qryReportAVUMSD.MOS_ID = 


qryReportAVUMSE.MOS_ID)


ORDER BY qryReportAVUMSD.EI_ID, qryReportAVUMSD.MOS_ID;

I have some records not counted in my data set because there are no records in qryReportAVUMSE but records are in are in qryReportAVUMSD. Any help will appreciated.

[pbaldy]

If they will be in qryReportAVUMSD, try changing INNER to LEFT.

[mrfixit1170]

That did the trick......I can see the records total up. Thank you pbaldy for your suggestion.

[pbaldy]

Happy to help.