If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to get information that doesn't match by date
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. |
#2
|
|||
|
|||
Query to get information that doesn't match by date
I should add this is in Access 2007 linked to a SQL 2008 database.
|
#3
|
|||
|
|||
Query to get information that doesn't match by date
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. . |
Thread Tools | |
Display Modes | |
|
|