new query, new problem

[red skelton via AccessMonster.com]

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

 

[Allen Browne]

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))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[red skelton via AccessMonster.com]

Thank You Sir for the information. I'll look at using the subquery as stated
and will let you know how I get on.
VR,

Red

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))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Hi everyone,
I'm having a problem with a query. I have two times, a TimeFinished and a

[quoted text clipped - 17 lines]

this
makes sense and appreciate any help in advance.