R
red skelton via AccessMonster.com
Good Afternoon, I am stumped and hope someone can help me see the light :>)
Thanks for everyone's help to this point as it is appreciated. I am using a
subquery to extract information from a table. What I am (and have been)
trying to do is a query that, based on OR# and date, show me the time
difference between TimeOutOfOR and the next TimeInOR. I have boiled it down
to the following SQL, but the problem is, every once in a while, the query
will pull in a TimeInOR from another OR#. No rhyme nor reason but the
erroneous times, if displayed, is allways from OR#4. It pulls information
from OR#4 about 40% of the time, and the rest of the time the query runs fine.
Can anyone see a problem with my SQL or have any suggestions? Any help would
be greatly appreciated.
VR,
Red
SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.Name, Table1.Prefix,
Table1.SSN, Table1.[UCA Code], Table1.Surgeon, Table1.Procedure, Table1.
TimeOutOfOR, DateDiff("s",Table1!TimeOutOfOR,Table1_Dup!TimeInOR)/60 AS
Elapsed, Table1_Dup.TimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON Table1.Date = Table1_Dup.Date
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[enter OR#] And (Table1.[OR#]) Not Like "LandD") AND (
(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR#];
Thanks for everyone's help to this point as it is appreciated. I am using a
subquery to extract information from a table. What I am (and have been)
trying to do is a query that, based on OR# and date, show me the time
difference between TimeOutOfOR and the next TimeInOR. I have boiled it down
to the following SQL, but the problem is, every once in a while, the query
will pull in a TimeInOR from another OR#. No rhyme nor reason but the
erroneous times, if displayed, is allways from OR#4. It pulls information
from OR#4 about 40% of the time, and the rest of the time the query runs fine.
Can anyone see a problem with my SQL or have any suggestions? Any help would
be greatly appreciated.
VR,
Red
SELECT Table1.ID, Table1.Date, Table1.[OR#], Table1.Name, Table1.Prefix,
Table1.SSN, Table1.[UCA Code], Table1.Surgeon, Table1.Procedure, Table1.
TimeOutOfOR, DateDiff("s",Table1!TimeOutOfOR,Table1_Dup!TimeInOR)/60 AS
Elapsed, Table1_Dup.TimeInOR
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON Table1.Date = Table1_Dup.Date
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[enter OR#] And (Table1.[OR#]) Not Like "LandD") AND (
(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR#];