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)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"GMC -LSND" <gcoleman@legalassistfake.org> wrote in message 
news:uIoKMTAtKHA.3904@TK2MSFTNGP02.phx.gbl...
> 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?
>
>
>
>
>
> 

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.

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"GMC -LSND" <gcoleman@legalassistfake.org> wrote in message 
news:ueabKCXtKHA.4796@TK2MSFTNGP02.phx.gbl...
> 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.
> 

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. 

What does "not working" mean? What are you getting that you don't want?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"GMC -LSND" <gcoleman@legalassistfake.org> wrote in message 
news:uOBxjHYtKHA.3904@TK2MSFTNGP02.phx.gbl...
> 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.
>