My query is broken and I cant fix it :<(

[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#];

 

[MGFoster]

Sounds like a corrupt index. Try the Tools > Database Utilities >
Compact and Repair option in the data file.

Though I doubt this is the problem (as long as you're using the name
delimiters ([]) for the column name), you might want to rename the
column "OR#" to OR_nbr, 'cuz OR is an SQL keyword.

 

[red skelton via AccessMonster.com]

Thanks for your reply. Tried both of your suggestions below and it has the
same problem. If I do a straight query on the OR_Number and the date, the
bogus times does not pull. Its just when I run the SQL in original post does
the times display. It must have something to do with the WHERE portin of
the SQL because its doing the SELECT and FROM portions ok. Any other Ideas?

SELECT Table1.ID, Table1.Date, Table1.[OR_Number], 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_Number])=[enter OR_Number] And (Table1.[OR_Number)) AND (
(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR_Number];

Thanks again,
Red

Sounds like a corrupt index. Try the Tools > Database Utilities >
Compact and Repair option in the data file.

Though I doubt this is the problem (as long as you're using the name
delimiters ([]) for the column name), you might want to rename the
column "OR#" to OR_nbr, 'cuz OR is an SQL keyword.

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

[quoted text clipped - 21 lines]

(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR#];

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know what the problem is. You have a column name in the WHERE
clause w/o any comparison. Here's a rewriting of your query w/ the
WHERE corrected, and some PARAMETERS defined so Access doesn't have to
guess at the data type. Also, I believe your join should be on more
than just the [Date] column. Usually, joins like that are joined on the
table's Primary Key column(s). I haven't changed the Joins since I
don't know the PK for the table.

PARAMETERS [Enter Date From] Date, [Enter Date To:] Date, [enter
OR_Number] TEXT(255);

SELECT T1.ID, T1.[Date], T1.[OR_Number], T1.[Name], T1.Prefix,
T1.SSN, T1.[UCA Code], T1.Surgeon, T1.Procedure, T1.TimeOutOfOR,

DateDiff("s",T1.TimeOutOfOR,T2.TimeInOR)/60 AS Elapsed,
T2.TimeInOR

FROM Table1 AS T1 LEFT JOIN Table1 AS T2
ON T1.[Date] = T2.[Date]

WHERE T1.[Date] Between [Enter Date From] And [Enter Date To:]
AND T1.[OR_Number] = [enter OR_Number]

- -- AND T1.[OR_Number] <- this doesn't mean anything. Shud be:

AND T1.OR_Number <> "LandD"

AND T2.TimeInOR >= T1.TimeOutOfOR

ORDER BY T1.[Date], T2.OR_Number
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/9mR4echKqOuFEgEQJyugCggRWY59Eft7jWidgrYx8WAg5Pgv4An1U8
MlGuu9gv8ZSIzidG09Of3EPd
=ukgf
-----END PGP SIGNATURE-----

Thanks for your reply. Tried both of your suggestions below and it has the
same problem. If I do a straight query on the OR_Number and the date, the
bogus times does not pull. Its just when I run the SQL in original post does
the times display. It must have something to do with the WHERE portin of
the SQL because its doing the SELECT and FROM portions ok. Any other Ideas?

SELECT Table1.ID, Table1.Date, Table1.[OR_Number], 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_Number])=[enter OR_Number] And (Table1.[OR_Number)) AND (
(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR_Number];

Thanks again,
Red

Sounds like a corrupt index. Try the Tools > Database Utilities >
Compact and Repair option in the data file.

Though I doubt this is the problem (as long as you're using the name
delimiters ([]) for the column name), you might want to rename the
column "OR#" to OR_nbr, 'cuz OR is an SQL keyword.

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

[quoted text clipped - 21 lines]

(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR#];

 

[Red via AccessMonster.com]

Thank You for all your help, it finally works properly. It is appreciated :>)

Red

I don't know what the problem is. You have a column name in the WHERE
clause w/o any comparison. Here's a rewriting of your query w/ the
WHERE corrected, and some PARAMETERS defined so Access doesn't have to
guess at the data type. Also, I believe your join should be on more
than just the [Date] column. Usually, joins like that are joined on the
table's Primary Key column(s). I haven't changed the Joins since I
don't know the PK for the table.

PARAMETERS [Enter Date From] Date, [Enter Date To:] Date, [enter
OR_Number] TEXT(255);

SELECT T1.ID, T1.[Date], T1.[OR_Number], T1.[Name], T1.Prefix,
T1.SSN, T1.[UCA Code], T1.Surgeon, T1.Procedure, T1.TimeOutOfOR,

DateDiff("s",T1.TimeOutOfOR,T2.TimeInOR)/60 AS Elapsed,
T2.TimeInOR

FROM Table1 AS T1 LEFT JOIN Table1 AS T2
ON T1.[Date] = T2.[Date]

WHERE T1.[Date] Between [Enter Date From] And [Enter Date To:]
AND T1.[OR_Number] = [enter OR_Number]

- -- AND T1.[OR_Number] <- this doesn't mean anything. Shud be:

AND T1.OR_Number <> "LandD"

AND T2.TimeInOR >= T1.TimeOutOfOR

ORDER BY T1.[Date], T2.OR_Number

Thanks for your reply. Tried both of your suggestions below and it has the
same problem. If I do a straight query on the OR_Number and the date, the

[quoted text clipped - 31 lines]

(Table1_Dup.TimeInOR)>=[Table1]![TimeOutOfOR]))
ORDER BY Table1.Date, Table1_Dup.[OR#];