Access Query mysteriously omitting records

I have an MS Access Contact & Correspondence database consisting of a “Contacts” table (Name, phone, email, etc.) and a “Calls” table (Contact, Incoming/Outgoing, Time, Medium, Notes, etc.). The tables are linked on Contacts.ID = Calls.Contact

For example:

NAME            NUMBER      EMAIL
Michaelangelo   123-4567    [e-mail]
Donatelo        123-4567    [e-mail]
Leonardo        123-4567    [e-mail]
Raphael         123-4567    [e-mail]

CONTACT         TIME        IN/OUT
Michaelangelo   1/1/2019    Outgoing
Michaelangelo   1/15/2019   Incoming
Michaelangelo   2/1/2019    Outgoing
Michaelangelo   3/1/2019    Outgoing
Leonardo        1/1/2019    Outgoing
Leonardo        2/1/2019    Outgoing
Michaelangelo   3/15/2019   Incoming

I’m trying to build a query to report all the contacts information (just as when the table is opened up directly), but with a field showing the most recent Incoming and Outgoing correspondence.

So, for the above data:

NAME            LAST OUT    LAST IN     NUMBER      EMAIL
Michaelangelo   3/1/2019    3/15/2019   123-4567    [e-mail]
Donatelo                                123-4567    [e-mail]
Leonardo        2/1/2019                123-4567    [e-mail]
Raphael                                 123-4567    [e-mail]

So the results would independently identify the latest date of a correspondence record of both the incoming and outgoing types, and would return [nulls] wherever such a correspondence type did not yet exist.

I have a query that is working, mostly. However it seems to be mysteriously omitting certain records for which there are no records in ‘Calls’. Not all such records, mind you.

My existing, troubled code is shown below. I have also tried moving the WHERE statements inside the JOINS (before ON); I have tried opening the WHERE statement with Calls.Time IS NULL OR Calls_1.Time IS NULL OR … , and several other versions of the WHERE statement.

Here is the existing query:

SELECT Contacts.Name_First, Contacts.Name_Last, Max(Calls.Time) AS [Last Incoming], Max(Calls_1.Time) AS [Last Outgoing]
FROM (Contacts 
LEFT OUTER JOIN Calls AS Calls_1 ON Contacts.ID = Calls_1.Contact ) 
LEFT OUTER JOIN Calls ON Contacts.ID = Calls.Contact 
WHERE (
(((Calls.Outgoing_Incoming)="Incoming")  OR  Calls.Outgoing_Incoming IS NULL)
AND 
(((Calls_1.Outgoing_Incoming)="Outgoing") OR Calls_1.Outgoing_Incoming IS NULL)
)
GROUP BY Contacts.Name_First, Contacts.Name_Last;

The full ‘Contacts’ table has 361 records. The intended result is that all 361 records will be returned, whether they have corresponding records in ‘Calls’ or not.

In fact only 208 records are returned. Many of these do not have corresponding ‘Calls’ records, telling me that the OR NULL statements are working, at least partly. I cannot find any consistent distinction between the records that are omitted verses the records that are returned.

Answer

Do the aggregation before doing the JOIN. Then you only need to aggregate once:

SELECT c.Name_First, c.Name_Last, ca.Last_Incoming, ca.Last_Outgoing
FROM Contacts as c LEFT OUTER JOIN
     (SELECT Contact,
             MAX(IIF(Outgoing_Incoming IS NULL OR Outgoing_Incoming = "Incoming", Time, NULL)) as Last_Incoming,
             MAX(IIF(Outgoing_Incoming IS NULL OR Outgoing_Incoming = "Outgoing", Time, NULL)) as Last_Outgoing,
      FROM Calls 
      GROUP BY Contact
     ) as ca
     ON c.ID = c.Contact ;