I am working on a SQL statement that has me pulling out what is left of my hair. A client wants a callsheet where he can enter calls (with the date the call was made) to his contacts. The idea being, he has a page which displays any contact that has not been called in the last 90 days.
I have a Access table (CallNotesTable) with four columns: CallID, ContactID, DateEntered, CallNotes
The SQL statement seemed easy at first:
SELECT ContactID FROM CallNotesTable WHERE EnteredDate NOT BETWEEN Date() AND DateAdd('m',-3,date())
However, since all calls are dated when entered, this SQL statement pulls all DateEntered made prior to three months ago, even if there has been a subsequent call made within the last three months.
So what I need is for the SQL to differentiate between a ContactID that HAS a DateEntered prior to three month with no DateEntered AFTER three months.
I have looked at the WHERE EXISTS statements but to no avail; my mind starts to turn to goo. I think the WHERE EXISTS statement will run a sub query that will allow me to differentiate, but I can't seem to determine the correct syntax:
SELECT ContactID FROM CallNotesTable WHERE EXISTS (SELECT CallId FROM CallNotesTable WHERE EnteredDate NOT BETWEEN Date() AND DateAdd('m',-3,date())) AND NOT EXISTS (SELECT CallId FROM CallNotesTable WHERE EnteredDate BETWEEN Date() AND DateAdd('m',-3,date()))
ORDER BY ContactID
Can anyone out there give a SQL rookie a hint?