help with query on non existing record 1

[patrichek]

hello,
i have a call log that i need to make a report on that tells me which contacts that have not been called after a certain number of days.
I tried "is null" in the subject field but that only shows if the user hasn't input anything in the subject. How can i query a record if its non existing?
here's my code (bythe way i use the query builder).

SELECT Contacts.[Date received], Contacts.FirstName, Contacts.LastName, Contacts.WorkPhone, Contacts.HomePhone, Contacts.MobilePhone, Contacts.BestNumber, Calls.Subject
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.[Date received])=Date()-2));

thanks!

 

[stix4t2]

Use a left join to include all records from contacts and matching records from calls, then test for null. You also may want to use a less than instead of an equal to.

 

[patrichek]

how would i use the query builder to make the changes you suggested? sorry i don't know sql much.

thanks!

 

[patrichek]

Hey thanks!
i think i have it working with this:

SELECT Contacts.FirstName, Contacts.LastName, Contacts.WorkPhone, Contacts.HomePhone, Contacts.MobilePhone, Contacts.BestNumber, Calls.Subject, Contacts.[Date received]
FROM Contacts LEFT JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.Subject) Is Null) AND ((Contacts.[Date received])=Date()-1));