Grouping of data

[osullipa]

I have a table which stores meeting room booking information in half hour slots.
If someone books a 2 hour meeting then 4 records would be produced – one for each half hour. I want to produce a query which will group the data by room (ScheduleID) showing the max and min times (ie the initial start time and final end time) for each event and the event details. The table structure is as follows

ScheduleDetailsID, ScheduleID, CustomerID, ScheduleStartTime, ScheduleEndTime, meeting purpose

And the query I have tried is as follows:

SELECT [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, Min([Schedule Details].ScheduleStartTime) AS MinOfScheduleStartTime, Max([Schedule Details].ScheduleEndTime) AS MaxOfScheduleEndTime, [Schedule Details].[meeting purpose]
FROM [Schedule Details]
GROUP BY [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, [Schedule Details].[meeting purpose];

Can anyone tell me where I have gone wrong. It does not group the data as I want it to ie by room, then time, with only the initial start and final end times. Thanks in advance. Peter

 

[Matt Greatorex]

It looks like the field ScheduleDetailsID is the ID number of the record? If so, it's almost certainly unique. Therefore, grouping on it will, effectively, produce the same results as not grouping on anything, since all records will be displayed.

Try the following:

SELECT
[Schedule Details].ScheduleID,
[Schedule Details].CustomerID,
Min([Schedule Details].ScheduleStartTime) AS MinOfScheduleStartTime,
Max([Schedule Details].ScheduleEndTime) AS MaxOfScheduleEndTime, [Schedule Details].[meeting purpose]
FROM
[Schedule Details]
GROUP BY
[Schedule Details].ScheduleID,
[Schedule Details].CustomerID,
Min([Schedule Details].ScheduleStartTime),
Max([Schedule Details].ScheduleEndTime),
[Schedule Details].[meeting purpose];

 

[Studentos]

1) Remove "[Schedule Details].ScheduleDetailsID," from allover the query
2) Remove "[Schedule Details].[meeting purpose] " from allover the query

 

[Matt Greatorex]

Remove "[Schedule Details].[meeting purpose] " from allover the query

Isn't 'meeting purpose' needed for the 'event details' part?
If not, then please ignore that part of my suggested query.