Query to get information that doesn't match by date

  • Thread starter Thread starter GMC -LSND
  • Start date Start date
G

GMC -LSND

I have a table named TTIME with columns in it named: Casenum, Tidate,
Reason, Snum
I have a table named CLIENTSCASENOTES with columns: Casenum, CNdate, CNnote,
Snum.

the column TIdate, in the TTIME table stores it's dates as mm/dd/yyyy
the column CNdate, in the CLIENTSCASENOTES tables stores its dates as
mm/dd/yyyy with the time on the end.

What I am trying to do is link the TTIME table with the CLIENTSCASENOTES
table and then get the following out of the query: All the records from
TTIME that do not have a record in the CLIENTSCASENOTES table with date
matching the date on the TTIME record by casenum and by snum.

It is perplexing to me.
 
The format does not matter so long as they both are datatype DateTime.
Use this query to remove the time --
qryClientNotesNewDate --
SELECT DateValue(CLIENTSCASENOTES.CNdate) AS NewDate, CLIENTSCASENOTES.*
FROM CLIENTSCASENOTES;

Then left join the query to TTIME --
SELECT TTIME.Casenum, TTIME.Tidate, TTIME.Reason, TTIME.Snum
FROM TTIME LEFT JOIN qryClientNotesNewDate ON TTIME.Tidate =
qryClientNotesNewDate.NewDate AND TTIME.casenum =
qryClientNotesNewDate.casenum AND TTIME.snum = qryClientNotesNewDate.snum
WHERE qryClientNotesNewDate.NewDate Is Null AND
qryClientNotesNewDate.casenum Is Null AND qryClientNotesNewDate.snum IS Null;
 
Back
Top