Including Null values in multiple table search

[skurlock]

Good Day!I have a SQl background via Toad interface on an oracle DB and am having issues trying to make access do what I want due to minor idiosyncracies hoping most of what I port will be laughable to you pros.. at any rateI have two tables I am querying across, One is a list of names and Ids the second the more robust data set including in this case payments, dates etc. What I am trying to is design a query that will retrieve ALL names and then for each name sum up the payments that are made and additionally those outstanding. I think the problem is that for the outstanding sum I sum by requesting the date to be null, which excludes one of the entries from the person table rather than what Id prefer (which it does in standard sql) just return a 0 value. Is there any way to force the query to retyurn all of the names, and if a sum criteria yields a 0 or null becuase of its criteria, to just show that, rather than not including the whole row which otherwise would have a value? I am using the below which works fine other than excluding that value.. Thanks in advance for any help, feel free to laugh if its so easy its sad select person.NAME, sum(Investments.committment) AS Capital_Committments, temp.flowed AS Flowed_CapitalFrom Person, Investments, (select person_id, sum(committment) as flowed from Investments where date_flowed is not nullgroup by person_id) AS tempWhere person.person_id=Investments.person_idand person.person_id=temp.person_idGroup by person.NAME, Temp.flowed;

 

[bob fitz]

I don't know that this will work but the Nz() function is often used with null values. Try:
sum(Nz(Investments.committment,0))

 

[Cerial]

If you want all the Person rows, LEFT JOIN Person with the Investments table, as in
FROM Person LEFT JOIN Investments ON Person.person_id = Investments.person_id

You could also refer to the Investments table twice, using a different alias for each copy, rather than use a separate/inner query:
FROM (Person LEFT JOIN Investments ON Person.Person_Id = Investments.Person_Id) LEFT JOIN Investments AS Investments_1 ON Person.Person_Id = Investments_1.Person_Id


Other suggestion: Inconsistent naming convention - Person (singular) and Investments (plural)?