Prev: Difference between fields in query
Next: Best way to set up Query to get a percentage
From: GMC -LSND on 24 Feb 2010 12:36
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.


From: GMC -LSND on 24 Feb 2010 12:41
I should add this is in Access 2007 linked to a SQL 2008 database.


From: KARL DEWEY on 24 Feb 2010 17:30
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;

--
Build a little, test a little.


"GMC -LSND" wrote:

> I should add this is in Access 2007 linked to a SQL 2008 database.
>
>
> .
>
 | 
Pages: 1
Prev: Difference between fields in query
Next: Best way to set up Query to get a percentage