Re-attack

[red skelton via AccessMonster.com]

Hi everyone,
I am doing a re-attack on a message I posted her a couple of weeks ago. I'm
still trying to make this query work but no luck. I have tried every
combination I can think of to get this query to run. What I currently have
is:

NextStart: (SELECT Min(Dupe.OR Start) FROM Table1 AS Dupe WHERE Dupe.OR Start

Table1.TimeOutOfOR AND = Dupe.OR# AND Table1.OR#)

But I keep getting but keep getting the following message:

Syntex Error in query expression 'NextStart: (SELECT Min(Dupe.OR Start) FROM
Table1 AS Dupe WHERE Dupe.OR Start > Table1.TimeOutOfOR AND = Dupe.OR# AND
Table1.OR#)'

All I'm trying to do with query is to pull the next OR Start time.

It is probably something very obvious but after looking at it for awhile, I
cant see where the problem is. Any help would be greatly appreciated.

VR,

Red

Here is the original postings:

Hi everyone,
I'm having a problem with a query. I have two times, a TimeFinished and a
TimeStart. The senerio is that I have a room that could be used multiple
times during a day. What I'm trying to do with my query is see how much time
in minutes elapse it takes from the time a group finishes using the room
until the next group starts using the room. In my table, these two times are
on two seperate records as they are two seperate entities. For example;

TimeFinish is 0900 (when the first group leave the room) This information is
included in one record
TimeStart is 0945 (when the next group starts to use room) This infromation
is from the next record
The time I'm trying to pull from my query is 45 minutes. I tried to use
DateDiff("n",[TimeFinished],[TimeStart]) but all I get is an error. (FYI
Time start is a text field and TimeFinished is a date/Time field) Hope this
makes sense and appreciate any help in advance.

VR,

Red

Reply to this message
Allen Browne - 01-19-2006 16:04
Use a subquery to get a value from another record in the same table.

Type something like this into a fresh column in the Field row of your query,
replacing "Table1" with the name of your table:

NextStart: (SELECT Min(Dupe.[TimeStart]) FROM Table1 AS Dupe
WHERE Dupe.TimeStart >= Table1.TimeFinished
AND Dupe.RoomID = Table1.RoomID)

Once you have that working, you can extend it so it calculate the minutes
gap rather than the next start time:

MinutesGap: DateDiff("n", Table1.TimeFinished,
(SELECT Min(Dupe.[TimeStart]) FROM Table1 AS Dupe
WHERE Dupe.TimeStart >= Table1.TimeFinished
AND Dupe.RoomID = Table1.RoomID))

 

[Tom Ellison]

Dear Red:

When you have a column name or table name with anything other than letters
or digits, you must set it off in square brackets:

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

Now, what this last line is supposed to do, I have no clue. You'll need to
explain this logic.

Tom Ellison

Hi everyone,
I am doing a re-attack on a message I posted her a couple of weeks ago.
I'm
still trying to make this query work but no luck. I have tried every
combination I can think of to get this query to run. What I currently
have
is:

NextStart: (SELECT Min(Dupe.OR Start) FROM Table1 AS Dupe WHERE Dupe.OR
Start

Table1.TimeOutOfOR AND = Dupe.OR# AND Table1.OR#)

But I keep getting but keep getting the following message:

Syntex Error in query expression 'NextStart: (SELECT Min(Dupe.OR Start)
FROM
Table1 AS Dupe WHERE Dupe.OR Start > Table1.TimeOutOfOR AND = Dupe.OR#
AND
Table1.OR#)'

All I'm trying to do with query is to pull the next OR Start time.

It is probably something very obvious but after looking at it for awhile,
I
cant see where the problem is. Any help would be greatly appreciated.

VR,

Red

Here is the original postings:

Hi everyone,
I'm having a problem with a query. I have two times, a TimeFinished and a
TimeStart. The senerio is that I have a room that could be used multiple
times during a day. What I'm trying to do with my query is see how much
time
in minutes elapse it takes from the time a group finishes using the room
until the next group starts using the room. In my table, these two times
are
on two seperate records as they are two seperate entities. For example;

TimeFinish is 0900 (when the first group leave the room) This information
is
included in one record
TimeStart is 0945 (when the next group starts to use room) This
infromation
is from the next record
The time I'm trying to pull from my query is 45 minutes. I tried to use
DateDiff("n",[TimeFinished],[TimeStart]) but all I get is an error. (FYI
Time start is a text field and TimeFinished is a date/Time field) Hope
this
makes sense and appreciate any help in advance.

VR,

Red

Reply to this message
Allen Browne - 01-19-2006 16:04
Use a subquery to get a value from another record in the same table.

Type something like this into a fresh column in the Field row of your
query,
replacing "Table1" with the name of your table:

NextStart: (SELECT Min(Dupe.[TimeStart]) FROM Table1 AS Dupe
WHERE Dupe.TimeStart >= Table1.TimeFinished
AND Dupe.RoomID = Table1.RoomID)

Once you have that working, you can extend it so it calculate the minutes
gap rather than the next start time:

MinutesGap: DateDiff("n", Table1.TimeFinished,
(SELECT Min(Dupe.[TimeStart]) FROM Table1 AS Dupe
WHERE Dupe.TimeStart >= Table1.TimeFinished
AND Dupe.RoomID = Table1.RoomID))

 

[red skelton via AccessMonster.com]

Hi Tom, I was following the suggestion on the reply above. First, make this
query "work" then change it to reflect my real query which will be something
like;

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

What my ultimate goal is to find the time difference between TimeOutOfOR til
the next OR Start. There is multiple OR Rooms is the reason for the last
line (I Think).

I tried your code but still received the same sytex error but will keep
looking at it. Thanks for all your help.

Red

Dear Red:

When you have a column name or table name with anything other than letters
or digits, you must set it off in square brackets:

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

Now, what this last line is supposed to do, I have no clue. You'll need to
explain this logic.

Tom Ellison

Hi everyone,
I am doing a re-attack on a message I posted her a couple of weeks ago.

[quoted text clipped - 73 lines]

WHERE Dupe.TimeStart >= Table1.TimeFinished
AND Dupe.RoomID = Table1.RoomID))

 

[Michel Walsh]

Hi,


I don't try to understand the logic, but again, OR# is not a valid
field name, you have, for syntax, to use [ ] around it:

.... AND Dupe.[OR#] = Table1.[OR#]



In general, you should avoid using illegal field name. Use the CAPTION if
you want "nicely rendered" names, but for names that "developer" should be
the only one to see, try to use legal field name. That simplify the syntax
and minimize the risk of error


Hoping it may help,
Vanderghast, Access MVP

Hi Tom, I was following the suggestion on the reply above. First, make
this
query "work" then change it to reflect my real query which will be
something
like;

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

What my ultimate goal is to find the time difference between TimeOutOfOR
til
the next OR Start. There is multiple OR Rooms is the reason for the last
line (I Think).

I tried your code but still received the same sytex error but will keep
looking at it. Thanks for all your help.

Red

Dear Red:

When you have a column name or table name with anything other than letters
or digits, you must set it off in square brackets:

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

Now, what this last line is supposed to do, I have no clue. You'll need
to
explain this logic.

Tom Ellison

Hi everyone,
I am doing a re-attack on a message I posted her a couple of weeks ago.

[quoted text clipped - 73 lines]

WHERE Dupe.TimeStart >= Table1.TimeFinished
AND Dupe.RoomID = Table1.RoomID))

 

[red skelton via AccessMonster.com]

Thanks for the reply,
I see now where I was getting the syntax error, and now the query runs but,
its not pulling the correct numbers. for example, I have an TimeOutOfOR of 9:
00 and the next start OR time of 9:20. While the minute gap should be 20
minutes, the query displays 4 minutes. My next record shows TimeOutOfOR of 11:
00 and the next start OR time of 11:30 where the minute gap should be 30
minutes, the query shows 2 minutes. I have included my current query and the
SQL statement behind the query. Again, I have tried different query
structures but I can seem to make it come out correctly.

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

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 AND Dupe.[OR#] = Table1.
[OR#])) AS MinutesGap
FROM Table1
WHERE (((Table1.Date) Between [Enter Date From] And [Enter Date To:]) AND (
(Table1.[OR#])=[Enter OR#]))
ORDER BY Table1.TimeOutOfOR;

Does anyone have an idea on how to make this query work?

Thanks very much for your time,

Red

Hi,

I don't try to understand the logic, but again, OR# is not a valid
field name, you have, for syntax, to use [ ] around it:

... AND Dupe.[OR#] = Table1.[OR#]

In general, you should avoid using illegal field name. Use the CAPTION if
you want "nicely rendered" names, but for names that "developer" should be
the only one to see, try to use legal field name. That simplify the syntax
and minimize the risk of error

Hoping it may help,
Vanderghast, Access MVP

Hi Tom, I was following the suggestion on the reply above. First, make
this

[quoted text clipped - 38 lines]