Query is not pulling correct information... Help Please

[red skelton via AccessMonster.com]

Good Afternoon,
I am having a problem with a query and will first thank everyone that has
helped so far. My problem is that the query isn't pulling the correct
information I need. I think, instead of looking at just the data entered in
my between date, it is looking at the entire table as the times it pulls are
in the table in a different day. My query is as follows:

MinutesGap: DateDiff("n",[Table1].[TimeOutOfOR],(SELECT Min(Dupe.[OR Start])
FROM Table1 AS Dupe WHERE Dupe.[OR Start]>= Table1.TimeOutOfOR))

and

NextStart: ((SELECT Min(Dupe.[OR Start]) FROM Table1 AS Dupe WHERE Dupe.[OR
Start]>= Table1.TimeOutOfOR))

my SQL is:

SELECT Table1.Name, Table1.Date, Table1.SSN, Table1.[OR#], Table1.Surgeon,
Table1.Procedure, Table1.[UCA Code], Table1.[OR Start], Table1.TimeOutOfOR,
DateDiff("n",[Table1].[TimeOutOfOR],(SELECT Min(Dupe.[OR Start]) FROM Table1
AS Dupe WHERE Dupe.[OR Start]>= Table1.TimeOutOfOR)) AS MinutesGap, ((SELECT
Min(Dupe.[OR Start]) FROM Table1 AS Dupe WHERE Dupe.[OR Start]>= Table1.
TimeOutOfOR)) AS NextStart
FROM Table1
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[Enter OR#]))
ORDER BY Table1.TimeOutOfOR;

What I'm trying to do is see how much time passes between the TimeOutOfOR and
the next OR Start time. For example, my first TimeOutOfOR is 8:15 and my
next OR Start time is 8:30 the Minutesgap should be 15 minutes and the next
start time of 8:30, but I get a MinuteGap of 1 minute and a NextStart of 8:31
(which is an OR start time but on another day)

I hope I have not totally confused everyone but any help to make this query
just look at the between time for the next OR Start time and not the entire
table.

Thanks for your time,

Red

 

[Stuart Bratesman]

One answer would be to create a pair of nested queries.

The first query would join two copies of Table1 by linking on the date of
the operation. The query would use a WHERE clause to limit the selection to
only those rows where the StartTime of operations appearing in the second
copy of Table1 was greater-than-or-equal-to the EndTime of that same day's
operations appearing in the first copy of Table1.

Note that this first query will display multiple rows for each operation
performed on Day N. If your first operation ended at 9:00 AM, this first
query will list a row for each one of the operations that began at or after
9:00 AM throughout the day.

One more step before we go to the second query - you will want the first
query to calculate the ElapsedTime between the EndTime of one operation and
the StartTime of each of the subsequent operations that followed during the
rest of that day.

Second Query

This one is easy. Just create a Group By query that uses the results of the
first query as it's data source and limits the rows to any given operation's
minimum StartTime (of all of that day's following operations) and minimum
ElapsedTime between each operation and all of that day's following operations.

Query1:

Notes:
* The two copies of Table1 are referred to as Table1 and Table1_Dup; and
* Access stores time in units of one day. Therefore, in Access one hour
is worth 0.041667 (1/24) and a minute = 0.000694 = 1 / (24 *60), hence the
math in the elapsed time calculation, below.

SELECT Table1.ID, Table1.ORdate, Table1.EndTime, Table1_Dup.StartTime,
Int(([Table1_Dup]![StartTime]-[Table1]![EndTime])*24*60) AS Elapsed
FROM Table1 LEFT JOIN Table1 AS Table1_Dup ON
Table1.ORdate = Table1_1.ORdate
WHERE (((Table1_Dup.StartTime)>=[Table1]![EndTime]))
ORDER BY Table1.ORdate, Table1.EndTime, Table1_Dup.StartTime;

Query 2:

SELECT Query1.ID, Query1.ORdate, Query1.EndTime, Min(Query1.StartTime) AS
NextStart, Min(Query1.Elapsed) AS ElapsedTime
FROM Query1
GROUP BY Query1.ID, Query1.ORdate, Query1.EndTime;

Have fun!

--
Stuart Bratesman, Jr., MPP
Muskie School of Public Service
Univ. of Southern Maine
Portland, Maine

Good Afternoon,
I am having a problem with a query and will first thank everyone that has
helped so far. My problem is that the query isn't pulling the correct
information I need. I think, instead of looking at just the data entered in
my between date, it is looking at the entire table as the times it pulls are
in the table in a different day. My query is as follows:

MinutesGap: DateDiff("n",[Table1].[TimeOutOfOR],(SELECT Min(Dupe.[OR Start])
FROM Table1 AS Dupe WHERE Dupe.[OR Start]>= Table1.TimeOutOfOR))

and

NextStart: ((SELECT Min(Dupe.[OR Start]) FROM Table1 AS Dupe WHERE Dupe.[OR
Start]>= Table1.TimeOutOfOR))

my SQL is:

SELECT Table1.Name, Table1.Date, Table1.SSN, Table1.[OR#], Table1.Surgeon,
Table1.Procedure, Table1.[UCA Code], Table1.[OR Start], Table1.TimeOutOfOR,
DateDiff("n",[Table1].[TimeOutOfOR],(SELECT Min(Dupe.[OR Start]) FROM Table1
AS Dupe WHERE Dupe.[OR Start]>= Table1.TimeOutOfOR)) AS MinutesGap, ((SELECT
Min(Dupe.[OR Start]) FROM Table1 AS Dupe WHERE Dupe.[OR Start]>= Table1.
TimeOutOfOR)) AS NextStart
FROM Table1
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[Enter OR#]))
ORDER BY Table1.TimeOutOfOR;

What I'm trying to do is see how much time passes between the TimeOutOfOR and
the next OR Start time. For example, my first TimeOutOfOR is 8:15 and my
next OR Start time is 8:30 the Minutesgap should be 15 minutes and the next
start time of 8:30, but I get a MinuteGap of 1 minute and a NextStart of 8:31
(which is an OR start time but on another day)

I hope I have not totally confused everyone but any help to make this query
just look at the between time for the next OR Start time and not the entire
table.

Thanks for your time,

Red