Combining Queries - DB Issue

[Crdmster]

Hey yall, I'm running into this issue quite often so any advice is appreciated. There could be an easy solution, but I havn't found it yet.

I have two queries:
1) Query 1 contains the project ID, task ID, & budget.
2) Query 2 contains the project ID, task ID, & the total consumed hours.

I want to combine these two queries (soon to be Query 3) in order to determine the remaining hours per task ([budget] - [consumed hours]).

The issue:
1) If Query 2 does not contain a value for total consumed hours then Query 3 will not list all of the budgeted hours.



~I've attached a pdf showing what I want Query 3 to look like, as well as what Queries 1 & 2 look like.


MS Access Query Issue.pdf

[JoeM]

I cannot download your file, but you should be able to use the NZ function to return a zero in the case of missing data, i.e.

=Nz([total consumed hours],0)+0

[pbaldy]

You need to edit the joins between the queries. Right or double click on each and the appropriate choice should be obvious.

[Crdmster]

Hey Joe, thanks for the quick response! Just to test it out, I've added the Nz() function around ALL of the variables in the SQL statement and my end results is still only 3 rows.

Should I upload the attachment is a different format for viewing (seeing the image helps to illustrate the issue)? I made it a pdf in hopes that it would be easier to view.

[JoeM]

Hey Joe, thanks for the quick response! Just to test it out, I've added the Nz() function around ALL of the variables in the SQL statement and my end results is still only 3 rows.

See Paul's comments above. You do not want to use an INNER JOIN in this case, as that only returns the matches. You want an Left Outer Join from Query1 to Query2. You use the Nz in conjunction with that.

[Crdmster]

... You do not want to use an INNER JOIN in this case, as that only returns the matches. You want an Left Outer Join from Query1 to Query2. ...

Thanks Paul & Joe, that last statement you made is what I was missing. Thanks alot!!!

[pbaldy]

No problemo.

[JoeM]

You are welcome.