subquery in filter

[PETER]

I have a database that records room bookings in half hour slots. A 1 hour
booking uses 2 records -one for each half hour. The allocation report would
also show 2 records. I have tried the following query to see if I can
display a single line showing for each room the initial start time and the
final end time as one record. I am having problems with the filter. Can
anyone tell me where I have gone wrong? Thanks in advance. Peter.

PS Sorry if this appears twice - I had a problem sending it the first time
and am not sure if message went!

SELECT Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
WHERE ScheduleID =
(SELECT (distinct(ScheduleID) FROM Schedule Details);

 

[Ofer Cohen]

Try and change the equal sign to In, and the table name in the sub query need
to have square brackets if it consist of two words and seperate.
The only thing I don't understand, why are you using a sub query with the
same table?


SELECT Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
WHERE ScheduleID In
(SELECT (distinct(ScheduleID) FROM [Schedule Details])

 

[Lucas Kartawidjaja]

Hi there

By any chance when you are running this query, it returns more than one row?
SELECT distinct(ScheduleID) FROM [Schedule Details]

If yes then you might want to consider, running the following query instead:

SELECT Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
WHERE ScheduleID IN
(SELECT distinct(ScheduleID) FROM [Schedule Details]);

But I think you might want to try this query instead:

SELECT ScheduleID, Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
GROUP BY ScheduleID;

Lucas

 

[PETER]

Thanks to both of you.

Hi there

By any chance when you are running this query, it returns more than one row?
SELECT distinct(ScheduleID) FROM [Schedule Details]

If yes then you might want to consider, running the following query instead:

SELECT Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
WHERE ScheduleID IN
(SELECT distinct(ScheduleID) FROM [Schedule Details]);

But I think you might want to try this query instead:

SELECT ScheduleID, Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
GROUP BY ScheduleID;

Lucas

I have a database that records room bookings in half hour slots. A 1 hour
booking uses 2 records -one for each half hour. The allocation report would
also show 2 records. I have tried the following query to see if I can
display a single line showing for each room the initial start time and the
final end time as one record. I am having problems with the filter. Can
anyone tell me where I have gone wrong? Thanks in advance. Peter.

PS Sorry if this appears twice - I had a problem sending it the first time
and am not sure if message went!

SELECT Min([Schedule Details].ScheduleStartTime), Max([Schedule
Details].ScheduleEndTime)
FROM [Schedule Details]
WHERE ScheduleID =
(SELECT (distinct(ScheduleID) FROM Schedule Details);