Query to list records that don't match

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

GMC -LSND

Access 2007 linked to SQL Server 2008 database.

Hello all,

I am trying to create a query that will join two tables. Time and Notes.

In the time table are all the time records people put in by case number and
listed by staff number. In the notes table are all the notes people put in
by a case number by staff number. They are joined by casenumber with "show
me all the records in time and only those in notes that are equal to time".

In the time table there is a field for DATE/TIME (tidate) - this field comes
out with only the date showing MM/DD/YYYY
In the notes table there is a field for DATE/TIME (cndate) - this field
comes out with MM/DD/YYYY TT:TT:TT PM

How would I create a query that will list all the records in the time table
that do not have a matching record with the same date/time in the notes
table (these are by a particular staff person which is also a field in each
table)? They never match because the date/time field in the notes table has
the time stamp on it and the time table does not. How do you trim off the
time part of the date in the query?
 
Rename your Time table. Time is a reserved word, and reserved words should
never be used for your own purposes. For a comprehensive list of names to
avoid (as well as a link to a free utility to check your application for
compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

The SQL you want is as follows. (Note that I kept your table name Time to
avoid confusion...)

SELECT Time.Field1, Time.Field2, Time.tidate, Notes.Field1, Notes.Field2,
Notes.cndate
FROM Time INNER JOIN Notes
ON (Time.tidate >= Notes.cndate) AND (Time.tiDate < Notes.cndate + 1)

or

SELECT Time.Field1, Time.Field2, Time.tidate, Notes.Field1, Notes.Field2,
Notes.cndate
FROM Time INNER JOIN Notes
ON Time.tidate = DateValue(Notes.cndate)
 
My table's name is TTIME, I was just trying to keep it simple. Thanks, I 'll
try this.
 
Neither of the queries work. I will attempt to explain better:

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.
 
Sorry: didn't read your requirements statement closely enough.

Here's a simplified version:

SELECT TTime.Field1, TTime.Field2, TTime.tidate
FROM TTime LEFT JOIN ClientsCaseNotes
ON (TTime.tidate >= ClientsCaseNotes.cndate)
AND (TTime.tiDate < ClientsCaseNotes.cndate + 1)
WHERE ClientsCaseNotes.Field1 IS NULL

I'm not sure whether you're going to need some additional criteria in the ON
clause.
 
Hmm, still not working. I need it to first look at it in the TTIME table by
casenum (which should match in both tables) and then give me all the records
that have TTIME.tidate and that don't have a matching casenum with
ClientsCasenote.CNdate with the date the same as the record in the
TTIME.tidate

I think the query you suggested joins them on the date column. I don't think
that will work because there can be many records with the same date in both
tables. When you throw in the criteria that they both have to have the same
casenum and the same snum field and the same date is when I have the
problems.

I want to list all the records in the clientscasenotes table that don' t
have the same casenum, snum and date as the TTIME table.
 
Back
Top